Sub queries

is a select statement that is nested inside a select, insert, update or delete statement. A select (outer query) that contains one or more sub queries ( the inner query) is referred as a nested query. The results of the outer query are evaluated based on the results of the inner sub queries.

The three different forms of sub queries in the where clause.
The first form operates on lists; it can return a list of multiple values. The second form uses comparison operators and must return single value unless it is modified with the keywords ANY or ALL. The third form serves as an existence test and is introduced by the keyword exists

Select empno, ename, sal from employee
where sal > ( select min(sal) from employee where deptno = 10)

Sub queries with IN
In this form, the inner query is evaluated first and returns a set of zero or more values to the outer query. The outer query then returns the rows that match the condition in WHERE clause based on the values the sub query returned.

Display all the employees in Dallas and New York
Select empno, ename from emp
where deptno in ( select deptno from dept where loc in ('Dallas','New York') )

Now we will see how exactly the above sub query gets executed in the server.
As we know the inner query executes first and feeds the result as the where clause for outer query.

Step1.
Execute the first query alone,
you will assume that 10, 20 are the result set for the query.
Setp2.
Feed the result set to the outer query. then the query becomes

Select empno, ename
from emp
where deptno in ( 10,20 )

Then it gives the result set back.

Sub queries with comparison operators
Sub queries can also be introduced with the standard operators used in comparison conditions.

Example
Find all the employees who gets the salary same as maximum salary in the department 20

Select empno, ename, deptno
from emp
where sal = (select max(sal) from emp where deptno = 20)

Example
Find the thrid maximum salary in the employee table.

Select max(sal)
from emp
where sal <
(select max(sal) from emp
where sal <
( select max(sal) from emp) )

In the above example the inner most query will get you the maximum salary, send that figure to the parent so that he gets next maximum, send that result to its parent so that he select max less than that salary.

Rules
Subqueries which has a comparison operator can return only a single value to the outer query, they often include aggregate functions.
> any
> all
>= any

Corelated Sub query

The difference between Sub query and Corelated Sub query is in Sub query inner most Sql executes first then it sends the results to the outer select where as in the corelated sub query outer select executes first and for every row we get the inner query gets executed. Usually we use corelated sub query to find whether the related record exists in the other table.

Suppose we want a result set like deptname, location in which there are at least one employee exists, we can write a correlated sub query.

In the outer select get all the department from department table, then try to match each department record to the employee table, if we hit a match there is an employee in that department, if not there is no employee in that department.

Select dname, loc from dept
where deptno exists
(select * from employee where deptno = dept.deptno)

If you want a result set for department in which there are no employee works then

Select dname, loc
from dept
where deptno not exists
(select * from employee where deptno = dept.deptno)

In the Correlated sub query inner select always returns the Boolean value TURE or FALSE.
If you use EXISTS in the outer query and if the inner select returns TRUE (i.e. at least 1 row found ) then the row is selected, if the inner select returns FALSE then the row is discarded.
If you use NOT EXISTS in the outer query and if the inner select returns FALSE (i.e. 0 rows for that select) then the row is selected, if the inner select returns TRUE then the row is discarded.

Sub Queries in DELETE, UPDATE and INSERT
Sub queries can also be used in the where clause of the data modification statements INSERT, DELETE and UPDATE the same way they are used in the SELECT statements.

Delete departments if there are no employee exists in that department.

Delete from department
where deptno not in ( select distinct deptno from emp )

Rules of Sub queries
The Select list of a sub query can usually include only one column name or expression

The only exceptions are when the sub query is introduced with EXISTS or the IN keyword. Then you can use a select * or several columns in the target list.

Sub queries introduced with unmodified comparison operators cannot have the GROUP BY or HAVING clauses.

The DISTINCT keyword cannot be used with sub queries that include a GROUP BY clause