ORACLE SQL PLSQL SUB QUERY training institutes in kukatpally hyderabad

SQL Subquery

The Subquery or Inner query is an SQL query placed inside another SQL query. It is embedded in the HAVING or WHERE clause of the SQL statements.

Following are the important rules which must be followed by the SQL Subquery:

1. The SQL subqueries can be used with the following statements along with the SQL expression operators:

  • SELECT statement,
  • UPDATE statement,
  • INSERT statement, and
  • DELETE statement.

2. The subqueries in SQL are always enclosed in the parenthesis and placed on the right side of the SQL operators.

Advertisement

3. We cannot use the ORDER BY clause in the subquery. But, we can use the GROUP BY clause, which performs the same function as the ORDER BY clause.

4. If the subquery returns more than one record, we have to use the multiple value operators before the Subquery.

5. We can use the BETWEEN operator within the subquery but not with the subquery.

Subquery with SELECT statement

In SQL, inner queries or nested queries are used most frequently with the SELECT statement. The syntax of Subquery with the SELECT statement is described in the following block:

  1. SELECT Column_Name1, Column_Name2, ...., Column_NameN
  2. FROM Table_Name WHERE Column_Name Comparison_Operator
  3. ( SELECT Column_Name1, Column_Name2, ...., Column_NameN
  4. FROM Table_Name WHERE condition;

Examples of Subquery with the SELECT Statement

Example 1: This example uses the Greater than comparison operator with the Subquery.

Advertisement

Let's take the following table named Student_Details, which contains Student_RollNo., Stu_Name, Stu_Marks, and Stu_City column.

Student_RollNo. Stu_Name Stu_Marks Stu_City
1001 Akhil 85 Agra
1002 Balram 78 Delhi
1003 Bheem 87 Gurgaon
1004 Chetan 95 Noida
1005 Diksha 99 Agra
1006 Raman 90 Ghaziabad
1007 Sheetal 68 Delhi

The following SQL query returns the record of those students whose marks are greater than the average of total marks:

  1. SELECT * FROM Student_Details WHERE Stu_Marks> ( SELECT AVG(Stu_Marks ) FROM Student_Details);

Output:

Student_RollNo. Stu_Name Stu_Marks Stu_City
1003 Bheem 87 Gurgaon
1004 Chetan 95 Noida
1005 Diksha 99 Agra
1006 Raman 90 Ghaziabad

Example 2: This example uses the IN operator with the subquery.

Let's take the following two tables named Faculty_Details and Department tables. The Faculty_Details table contains ID, Name, Dept_ID, and address of faculties. And, the Department table contains the Dept_ID, Faculty_ID, and Dept_Name.

Faculty_ID Name Dept_ID Address
101 Bheem 1 Gurgaon
102 Chetan 2 Noida
103 Diksha NULL Agra
104 Raman 4 Ghaziabad
105 Yatin 3 Noida
106 Anuj NULL Agra
107 Rakes 5 Gurgaon
Dept_ID Faculty_ID Dept_Name
1 101 BCA
2 102 B.Tech
3 105 BBA
4 104 MBA
5 107 MCA
  1. SELECT * FROM Department WHERE Faculty_ID IN (
  2. SELECT Faculty_ID FROM Faculty WHERE City = 'Noida' OR City = 'Gurgaon' ) ;

Output:

Dept_ID Faculty_ID Dept_Name
1 101 BCA
2 102 B.Tech
3 105 BBA
5 107 MCA

Subquery with the INSERT statement

We can also use the subqueries and nested queries with the INSERT statement in Structured Query Language. We can insert the results of the subquery into the table of the outer query. The syntax of Subquery with the INSERT statement is described in the following block:

  1. INSERT INTO Table_Name SELECT * FROM Table_Name WHERE Column_Name Operator (Subquery);

Examples of Subquery with the INSERT Statement

Example1: This example inserts the record of one table into another table using subquery with WHERE clause.

Let's take Old_Employee and New_Employee tables. The Old_Employee and New_Employee table contain the same number of columns. But, both the tables contain different records.

Emp_ID Emp_Name Emp_Salary Address
1001 Akhil 50000 Agra
1002 Balram 25000 Delhi
1003 Bheem 45000 Gurgaon
1004 Chetan 60000 Noida
1005 Diksha 30000 Agra
1006 Raman 50000 Ghaziabad
1007 Sheetal 35000 Delhi

Table: Old_Employee

Emp_ID Emp_Name Emp_Salary Address
1008 Sumit 50000 Agra
1009 Akash 55000 Delhi
1010 Devansh 65000 Gurgaon

Table: New_Employee

The New_Employee contains the details of new employees. If you want to move the details of those employees whose salary is greater than 40000 from the Old_Employee table to the New_Employee table. Then for this issue, you have to type the following query in SQL:

  1. INSERT INTO New_Employee SELECT * FROM Old_Employee WHERE Emp_Salary > 40000;

Now, you can check the details of the updated New_Employee table by using the following SELECT query:

  1. SELECT * FROM New_Employee;

Output:

Emp_ID Emp_Name Emp_Salary Address
1008 Sumit 50000 Agra
1009 Akash 55000 Delhi
1010 Devansh 65000 Gurgaon
1001 Akhil 50000 Agra
1003 Bheem 45000 Gurgaon
1004 Chetan 60000 Noida
1006 Raman 50000 Ghaziabad

Table: New_Employee

Example 2: This example describes how to use ANY operator with subquery in the INSERT Statement.

Here we have taken the New_Employee, old_Employee, and Department table.

The data of the New_Employee table is shown in the following table:

Emp_ID Emp_Name Emp_Salary Dept_ID
1008 Sumit 50000 401

Table: New_Employee

The data of the old_Employee table is shown in the below table:

Emp_ID Emp_Name Emp_Salary Dept_ID
1001 Akhil 50000 404
1002 Balram 25000 403
1003 Bheem 45000 405
1004 Chetan 60000 402
1005 Ram 65000 407
1006 Shyam 55500 NULL
1007 Shobhit 60000 NULL

Table: Old_Employee

The data of Department table is shown in the below table:

Dept_ID Dept_Name Emp_ID
401 Administration 1008
402 HR 1004
403 Testing 1002
404 Coding 1001
405 Sales 1003
406 Marketing NULL
407 Accounting 1005

INSERT INTO New_Employee

SELECT * FROM Old_Employee

WHERE Emp_ID = ANY( SELECT Emp_ID FROM Department WHERE Dept_ID = 407 OR Dept_ID = 406 );

Now, check the details of the New_Employee table by using the following SELECT statement:

Output:

Emp_ID Emp_Name Emp_Salary Dept_ID
1008 Sumit 50000 401
1005 Ram 65000 407

Subquery with the UPDATE statement

The subqueries and nested queries can be used with the UPDATE statement in Structured Query Language for updating the columns of the existing table. We can easily update one or more columns using a subquery with the UPDATE statement.

Syntax of Subquery with the UPDATE statement

  1. UPDATE Table_Name SET Column_Name = New_value WHERE Value OPERATOR (SELECT COLUMN_NAME FROM TABLE_NAME WHERE Condition) ;

Example of Subquery with the UPDATE statement

This example updates the record of one table using the IN operator with Subquery in the UPDATE statement.

Let's take an Employee_Details and Department table.

The data of the Employee_Details table is shown in the following table:

Emp_ID Emp_Name Emp_Salary Dept_ID
1001 Akhil 50000 404
1002 Balram 25000 403
1003 Bheem 45000 405
1004 Chetan 60000 402
1005 Ram 65000 407
1006 Shyam 55500 NULL
1007 Shobhit 60000 NULL

Table: Employee_Details

The data of Department table is shown in the below table:

Dept_ID Dept_Name Emp_ID Dept_Grade
401 Administration 1008 B
402 HR 1004 A
403 Testing 1002 A
404 Coding 1001 B
405 Sales 1003 A
406 Marketing NULL C
407 Accounting 1005 A

The following updates the salary of those employees whose Department Grade is A:

  1. UPDATE Employee_Details SET Emp_SalaryEmp_Salary = Emp_Salary + 5000 WHERE Emp_ID IN ( SELECT Emp_ID FROM Department WHERE Dept_Grade = 'A' ) ;

The following query will show the updated data of the Employee_Details table in the output:

  1. SELECT * FROM Employee_Details ;

Output:

Emp_ID Emp_Name Emp_Salary Dept_ID
1001 Akhil 50000 404
1002 Balram 30000 403
1003 Bheem 50000 405
1004 Chetan 65000 402
1005 Ram 70000 407
1006 Shyam 55500 NULL
1007 Shobhit 60000 NULL

Table: Employee_Details

Subquery with the DELETE statement

We can easily delete one or more records from the SQL table using Subquery with the DELETE statement in Structured Query Language.

Syntax of Subquery with DELETE statement

  1. DELETE FROM Table_Name WHERE Value OPERATOR (SELECT COLUMN_NAME FROM TABLE_NAME WHERE Condition) ;

Example of Subquery with DELETE statement

This example deletes the records from the table using the IN operator with Subquery in the DELETE statement.

Let's take an Employee_Details and Department table.

The data of the Employee_Details table is shown in the following table:

Emp_ID Emp_Name Emp_Salary Dept_ID
1001 Akhil 50000 404
1002 Balram 25000 403
1003 Bheem 45000 405
1004 Chetan 60000 402
1005 Ram 65000 407
1006 Shyam 55500 NULL
1007 Shobhit 60000 NULL
1008 Ankit 48000 401

Table: Employee_Details

The data of Department table is shown in the below table:

Dept_ID Dept_Name Emp_ID Dept_Grade
401 Administration 1008 C
402 HR 1004 A
403 Testing 1002 C
404 Coding 1001 B
405 Sales 1003 A
406 Marketing NULL C
407 Accounting 1005 C

The following query deletes the record of those employees from the Employee_Details whose Department Grade is C:

  1. DELETE FROM Employee_Details WHERE Emp_ID IN ( SELECT Emp_ID FROM Department WHERE Dept_Grade = 'C' ) ;

The following query will show the updated data of the Employee_Details table in the output:

  1. SELECT * FROM Employee_Details ;

Output:

Emp_ID Emp_Name Emp_Salary Dept_ID
1001 Akhil 50000 404
1003 Bheem 45000 405
1004 Chetan 60000 402
1006 Shyam 55500 NULL
1007 Shobhit 60000 NULL

Table: Employee_Details

Next TopicSQL View