SQL Sub Queries

Subquery or Inner Query or Nested Query is a query in a quary, A subquery is usually added in the WHERE Clause of sql statement. Most of the time, a subquery is used when you now how to search for a value using a SELECT statement, but do not know the exact value.

 A query result can be used in a condition of a Where clause. In such case, a query is called a subquery and complete SELECT statement is called a nested query. We can also used subquery can also be placed within HAVING clause. But subquery cannot be used with ORDERBY clause.

Subqueries are queries nested inside other queries, marked off with parentheses, and sometimes referred to as “inner” queries within “outer” queries. Most often, you see subqueries in WHERE or HAVING clauses.

There are basically three types of subqueries are:
• Single Row Subqueries
• Multiple Row Subqueries
• Multiple Column Subqueries
Things to Remember:
• Subqueries are queries nested inside other queries, marked off with parentheses.
• The result of inner query will pass to outer query for the preparation of final result.
• ORDER BY clause is not supported for Nested Queries.
• You cannot use Between Operator.
• Subqueries will always return only a single value for the outer query.
•  A sub query must be put in the right hand of the comparison operator.
•  A query can contain more than one sub-query.

Syntax: 
SELECT <column, .. 
FROM <table> 
WHERE expression operator 
(SELECT <column, ... 
FROM <table> 
WHERE <condition) 

Single Row Subqueries

The single-row subquery returns one row. A special case is the scalar subquery, which returns a single row with one column. Scalar subqueries are acceptable (and often very useful) in virtually any situation where you could use a literal value, a constant, or an expression.

The single row query uses any operator in the query .i.e. (=, <=, >= <>, <, >). If any of the operators in the preceding table are used with a subquery that returns more than one row, the query will fail.

• Suppose you want to find out the ename, job, sal of the employees whose salaries are less than that of an employee whose empno= 7369 from obviously EMP table. Now you need to perform two queries in order to get the desired result. In the first query:

1)  We will find out the salary of the employee whose empno=7369. The query is as under:

SQL> SELECT SAL FROM EMP WHERE EMPNO=7876; 

After execution it will return the salary of a given empno and it will be 1100.

2) Now in the second query we will apply the condition from which we will find the ename, job, and sal. We will use the query as under

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE SAL<1100; 

After execution we will get two records. The above two queries can be used as a single query by using the concept of SUBQUERY. It will combine the result into a single query as under:

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE SAL < (SELECT SAL FROM EMP WHERE EMPNO= 7876);

After execution you can see that the output will remain the same. First of all the inner query will be performed and the result returned by it will be used by the outer query to return the final result.

Display the Ename, Job, Sal from EMP working in the LOC ‘CHICAGO’ and salary is less than the salary of whose empno=7876.

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE DEPINO = (SELECT DEPTNO FROM DEPT WHERE LOC=‘CHICAGO’) AND SAL < (SELECT SAL FROM EMP WHERE EMPNO=7876);

Group Functions in the Subquery

You can also use the aggregate functions in the subquery because it will produce only single result.

To retrieve the details of the employee holding the minimum salary.

SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP; 

To retrieve the details of employees whose salary is greater than the minimum salary of the employees working in dept number 20 and also he is not working in dept number 20.

SQL> SELECT ENAME, SAL FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30)AND DEPTNO<>30;

The following statement returns all departments in where the minimum salary is more than the minimum salary in the department 20.

SQL> SELECT DEPTNO, MIN (SAL) FROM EMP GROUP BY DEPTNO HAVING MIN (SAL)> (SELECT MIN (SAL)FROM EMP WHERE DEPTNO=20);
Display the detail of department whose manager Ecode=‘7698’.
SQL> SELECT * FROM DEPT WHERE DEPTNO = (SELECT DISTINCT DEPTNO FROM EMP WHERE MGR=7698);

On execution the inner query will give the deptno whose manager’s ecode=’7698′. Without distinct clause the inner query would have return more than one row as there are number of employees whose manager ecode=’7698′.

Multiple-Row Subqueries

Multiple-row subqueries return sets of rows. These queries are commonly used to generate result sets that will be passed to a DML or SELECT statement for further processing. Both single-row and multiple-row subqueries will be evaluated once, before the parent query is run.

Since it returns multiple values, the query must use the set comparison operators (IN, ALL, ANY). If you use a multi row sub query with the equals comparison operators, the database will return an error if more than one row is returned. The operators in the following table can use multiple-row subqueries:

IN Operator

The IN operator return TRUE, if the comparison value is contained in the list; in this case, the results of the subquery.

The following statement finds the employees whose salary is the same as the minimum salary of the employees in some department.

SQL> SELECT ENAME,SAL FROM EMP WHERE SAL IN (SELECT MIN (SAL) FROM EMP GROUP BY DEPTNO); 

On execution first of all the inner query will return the minimum salary of all the departments and return to outer query. After getting the result from inner query it will compare the result of inner query and return the number of employees

• List all the employees Name and Sal working at the location ‘DALLAS’.

SQL> SELECT ENAME, SAL, JOB FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC =‘DAllAS‘);

Any Operator

The ANY operators work with the equal operators. The ANY operator returns TRUE if the comparison value matches any of the values in the list.

• Display the employees whose salary is more than the maximum salary of the employees in any department.

SQL> SELECT ENAME, SAL FROM EMP WHERE SAL) ANY (SELECT MAX (SAL) FROM EMP GROUP BY DEPTNO);

Display the employees whose salary is greater than any ‘manager’ and he is not a ‘manager’.

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE SAL> ANY (SELECT SAL FROM EMP WHERE JOB=‘MANAGER’) AND JOB <>‘MANAGER’;

All Operator

The ALL operator returns TRUE only if the comparison value matches all the values in the list. It compares a value to every value returned by a query. The condition evaluates to true if subquery doesn’t yield any row.

Display the employee detail with salary less than those whose job is ‘manager’.

SQL> SELECT empno, ename, job, sal FROM EMP WHERE sal < all (SELECT sal FROM EMP WHERE job = ‘MANAGER’) AND job <> ‘MANAGER’;

On execution the query will first select the salary of employees who are ‘managers’, then it give the result to outer query and it will display the empno, ename, job, salary of those employees who have less salary than any ‘manager’.

Multiple – Column Subquery

A subquery that compares more than one column between the parent query and subquery is called the multiple column subqueries. In multiple-column subqueries, rows in the subquery results are evaluated in the main query in pair-wise comparison. That is, column-to-column comparison and row-to-row comparison.

• List the employees that makes the same salary as other employee with empno=752l with the same job also.

SQL> SELECT ENAME, JOB, SAL, EMPNO FROM EMP WHERE (JOB, SAL) IN (SELECT JOB, SAL FROM EMP WHERE EMPNO=7521);

On execution it is clear that first of all it will select the job, sal from employee table whose empno is 7521. then it handover the result to outer query and because now we will use two columns for comparison so after comparing each row with the empno 7521 it will return the desired result.

Correlated Subquery

A correlated subquery has a more complex method of execution than single- and multiple-row subqueries and is potentially much more powerful. If a subquery references columns in the parent query, then its result will be dependent on the parent query. This makes it impossible to evaluate the subquery before evaluating the parent query.

Consider this statement, which lists all employees who earn less than the average salary:

SQL> Select Last_name from employees Where salary < (select avg (salary) from employees); 

The single-row subquery need only be executed once, and its result substituted into the parent query.

Select the highest paid employee from the list of each department.

SQL> SELECT ENAME, SAL, DEPTNO FROM EMP E1 WHERE SAL = (SELECT MAX (SAL) FROM EMP WHERE DEPTNO=E1.DEPTNO) ORDER BY DEPTNO;

The subquery will get executed for each row returned in the parent key. We used the alias for the name inside the subquery.

The flow of execution is as follows:

• Start at the first row of the EMPLOYEES table.

• Read the DEPARTMENT_ID and SALARY of the current row.

• Run the subquery using the DEPARTMENT_ID from step 2.

• Compare the result of step 3 with the SALARY from step 2, and return the row if the SALARY is less than the result.

• Advance to the next row in the EMPLOYEES table.

• Repeat from step 2.

A single-row or multiple-row subquery is evaluated once, before evaluating the outer query; a correlated subquery must be evaluated once for every row in the outer query. A correlated subquery can be single- or multiple-row, if the comparison operator is appropriate.