Subqueries :

-> A query inside another query is called as subquery. (or) -> A subquery is a query which is nested inside a select,insert,update or delete statements. -> A subquery is also called as inner select.

Subqueries Rules :

Subquery is subjected to many restrictions: -> The select list of a subquery introduced with a comparison operator can include only one expression or column name (expect that EXISTS and IN operate on SELECT * or a list respectively) -> If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list. -> The ntext,text and image data types are not allowed in the select list of subqueries. -> The DISTINCT keyword cannot be used with subqueries that include GROUP BY. -> The compute and INTO clauses cannot be specified. -> ORDER BY can only be specified if TOP is also specified. NOTE: 32 levels of nesting is possible for sub queries.

How Subquery will be executed:

First innermost query will be evaluated and then the immediate level outer query will be evaluated. NOTE :An outer query can be an insert,update or delete.

Usual form of subqueries:

-> WHERE expression[NOT] IN (subquery) -> WHERE expression comparision_operator[ANY|ALL](subquery) -> WHERE [NOT] EXISTS (subquery) Query for displaying employees details where salary is greater than highest paid db department employees salary. ->select * from employees where salary > (select max(salary) from employees where dept='db') Query to display .net department employees whose salaries are greater than db department least paid employees salary. ->select * from employees where salary> (select min(salary) from employees where dept='db') and dept='.net' Query to display all employees salary are less than least paid android department employees salary and greater than db department employees salary. ->select* from employees where salary <(select min(salary) from employees where dept='android' and salary(select max(salary) from employees where dept='db'). Query for updating least paid db department employees salary with highest paid .net departments employees salary. ->update employees set salary =(select min(salary) from employees where dept='db') and salary (select max(salary) from employees where dept='.net') Query for deleting all the employees whose salary is greater than second highest paid db department employees salary. insert into @t select top 2 salary from employees where dept='db' orderby salary desc delete employees where salary>(select top1 salary from @t order by salary asc) Query for displaying third highest salary. declare @t table ( salary int, ) insert into @t select distinct top 3 salary from employees orderby salary desc select top1 salary fron @t order by salary asc. Query to display all employees whose salaries are greater than the corresponding department average salaries. ->select avg(salary), dept from employees group by dept select * from employees select e1.* from employees e1 where e1.salary>(select avg(e2.salary) from employees e2 where e1.dept=e2.dept.

Correlated subqueries:

-> In correlated subqueries inner query will depend on outer query for its input and outer query will depend on inner query for its input.So such kind of queries are called as correlated subqueries.