Sub queries in SQL Server

A sub query is a select statement containing another select statement.   The inner query will be executed first, and based on the result, the outer query (parent query) will be executed

1. Simple sub query
2. Nested sub query
3. Multiple sub query
4. Correlated sub query

1. Simple sub query: 

It is selected statement containing only one other select statement.

Q) --Get the second max sal from emp table.

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

--Get the second minimum sal from the temp table

select min(sal)from emp where sal >(select min(sal) from  emp) 

2. Nested Sub queries:  

It is a select statement containing another select statement which contains another select statement and so on....

A max of 32 select statements can be provided in a nested sub query.

 Q) get the third max sal from emp table.

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

--get the third minimum sal from emp table

select min(sal) from emp where sal >(select min(sal) from emp where sal >(select min(sal) from emp))

3. Multiple sub query:  

It is a select statement containing different select statements at different places.

Q) List out the employees who are having salary less than the max salary and also having hiredate greater than the hiredate of an employee who is having the max salary.

 [please check for a syntactical error!]

select empid,ename,sal,hiredate from emp where sal < (select max(sal) from emp) and hiredate > (select hiredate from emp where sal = (select max(sal) from emp))) 

4. Correlated Sub query:  

In a correlated sub query, the inner query will be executed for each record of the parent table.

Steps:

1) A record value from parent table will be passed to the inner query.
2) The inner query execution will be done based on that value
3) The result of the inner query will be sent back to the parent statement
 4) The parent query execution will be completed for that record value.

Q) Get the 4 th max sal from the emp table.

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

--5th max sal

select a.empid, a.ename, a.sal from emp a  where 4 = (select count(distinct(b.sal)) from emp b where a.sal < b.sal)

--6th max sal

select a.empid, a.ename, a.sal from emp a  where 5 = (select count(distinct(b.sal)) from emp b where a.sal < b.sal)  

--nth max sal

select a.empid, a.ename, a.sal from emp a  where n-1 = (select count(distinct(b.sal)) from emp b where a.sal < b.sal)