SUB QUERIES

S U B Q U E R I E S

A statement requesting data from database called as a query. As we are using select statement to request data from database, select statement is called as a query. Writing a select statement within another select statement is called as a Subquery. In a subquery the select statement that contains another select statement within it is called as outer query and the select statement that is within another select statement is called as Inner query. Inner query must be placed in parantheses. This is because inner query must be executed prior to outer query. Sub queries in SQL are of four types and are as follows.

Single Row Sub Queries
Multi Row Sub Queries
Nested Sub Queries
Correlated Sub Queries

1.SINGLE ROW SUBQUERIES :

If an inner query of a subquery returns only one value then that subquery is called as a Single Row Subquery.
Following example displays the employee details whose salary is maximum salary among all employees. In this example, inner query retrieves the maximum salary in EMP table and outer query compares salary of every employee with the salary returned by inner query. When employee salary is equal to the salary returned by inner query, then that employee row is displayed in output.

Example 1 : Select * from EMP where Sal = ( Select Max(Sal) from EMP );

Following example displays all employee details who are working in SALES deparetment. Because EMP table contains only department number and it does not contain department name, first we have to know the DEPTNO of SALES department. Inner query retrieves the DEPTNO of sales department from DEPT table and that DEPTNO is used by outer query to find employees working in SALES department.

Example 2 : Select * from EMP where Deptno=(Select Deptno from DEPT where Dname=’SALES’);

As inner query of above two subqueries returns only one value, these subqueries are called as single row subqueries.

2.MULTI ROW SUB QUERIES :

If an inner query of a subquery returns more than one value then that subquery is called as a Multi Row Subquery.
Following example finds the employees drawing maximum salary for each department. In this example, inner query returns the list of maximum salaries in all departments and outer query will compare employee salary with the list of salaries returned by inner query. If employee salary is equivalent to any one of the slary in the list of slaries returned by inner query, then that employee row is displayed in out put.

Example : Select * from EMP where Sal in ( Select Max(Sal) from EMP Group By Deptno );

But the result of this query is wrong. If maximum salaries of departments 10,20, and 30 are 1500,2800,5000 and an employee of department 30 has a monthly salary 2800, then the row of that employee is also retrieved even he is not drawing maximum salary in his department i.e. 5000. When working with multi-row subqueries, we must perform condition checking with more than one column and the above query corrected is as follows.
Example : Select * from EMP where (Deptno, Sal) in ( Select Deptno, Max(Sal) from EMP Group By Deptno);

In the above subquery, inner query returns more than one value. Hence this subquery is called as multi-row subquery. There are two special operators that can be used with Multi Row subqueries. They are Any/Some and All.

ANY / SOME : This operator can be used only with multi row subqueries. This operator returns true if the given condition is true with any one of the values retrieved by the inner query.
Following example finds all employees who are drawing more salary than any one of the employees working in Department 30. Inner query retrieves a list of slaries drawn by the employees working in department 30 and outer query will compare the salary of each employee with every value returned by inner query. If the given condition is true with any one of the values in the list of salaries returned by inner query, then that row is displayed in the output. If the given condition is not true with all values in the list returned by inner query, then that row is not displayed in the output.
Example : Select * from EMP where Sal > ANY(Select distinct Sal from EMP where Deptno=30);

ALL : This operator can also be used only with multi row subqueries. This operator returns true if the given condition is true with all values returned by inner queery.
Following example finds all employees whose salary is more than every employee working in Department 30. Inner query retrieves a list of slaries drawn by the employees working in department 30 and outer query will compare the salary of each employee with every value returned by inner query. If the given condition is true with all of the values in the list of salaries returned by inner query, then that row is displayed in the output. If the given condition is not true with any one of the value in the list returned by inner query, then that row is not displayed in the output.

Example : Select * from EMP where Sal > ALL(Select distinct sal from EMP where Deptno = 30 );

3. NESTED SUBQUERIES :

If an inner select statement of a subquery contains another select statement within it then that subquery is called as Nested subquery. Subqueries can be nested up to 255 levels.
Following example finds all employees whose salary is more than average salary of sales department. As department name does not existin EMP table, first we have to find the deptno of sales dapartment. Then we have to find the average salary of sales department by using deptno returned by inner most query. Finally this average salary is used by outer query to find the employees whose salary is more than this average salary.

Example : Select * from EMP where Sal > (Select AVG(Sal) from EMP where Deptno = (Select Deptno from DEPT where Dname = ‘SALES’ ));

4. CO-RELATED SUBQUERIES : If an inner query of a subquery contains an outer query column in the condition of where clause then that subquery is called as a co-related subquery.
Following example finds all employees whose Salary is more than average salary of the department in which they are working. Inner query finds the average salary of a department in which an employee, whose row is curently accessed by outer query and this average salary is compared to the salary of that employee and if his salary is more than average salary. Then his row is displayed in the output.

Example : Select * from EMP E where Sal > ( Select AVG(Sal) from EMP S Where E.Deptno = S.Deptno );

The difference between normal subqueries and correlated subqueries is that in normal subqueries the inner query is executed only once. But in correlated subqueries inner query is executed once per each row of the outer query.