Subquery in SQL Server

Subquery

A select statement that is written with in another select statement Is called Subquery.

Subquery must be enclosed in () to give priority for it and execute it first.

Subquey are classified into 4 types:-

1. Single row subquey
2. Multiple row subquey.
3. Nested sub query.
4. Co –related sub query.

1. Single row sub Query

When a subqueries return a single row then it is called as single row subquery.

Eg

Find the employee working in sales department ?
=>
Select * from emp where deptno=(select deptno from dept where dname=’sales’)

Eg

Find hightest salary of employee ?
=>
Select * from emp where sal=(select max(sal) from emp)

2. Multirow Subqueries

When a subquery return more than one group then it is called as multi row sub query

Eg

Find the highest salary employee deptwise?
=>
Select * from emp where sal in(select max(sal)from emp group by deptno)

O/P=>

Empno ename Dept No sal

Any Row some and ALL(=,!=,>,<,>=,<=)

With multi row subquery it is not possible with to use the operatos like (= and !=)because these operators can be used to compare with single value only but there is situations where you have to use thoese operation with uliti rows subquery and for that situation two predicate in ANY OR/All provided in sql Server.

The predicate ANY will returns true when the given condition is true with any one value is return by multi rows subquery and the predicate ALL will be return that only when the given condition is true with all values retruned by multi rows subquery.

Eg

Find the emp who are not working in dept 30,and whose salary is more than any one employee working in dept 30?

=>
Select * from emp where deptno !=30 and sal >any(select distinct sal from emp where dept no=30)

--------------OR-----------------

Select * from emp where deptno !=30 and sal> (select min(sal)from emp where deptno=30)

Eg

Find emp who are not working in department 30 and whose salary is more than the salary of all emp in department 30?

=>
Select * from emp where deptno !=30 and sal>all(select max(sal) from emp where deptno=30)

3. Nested sub query

A subquery that was written with in another sub query is called as nested sub query.


Eg:find the emp whose salary is more than average salary of sales department?
=>
Select * from emp where sal>(select avg(sal)from emp where deptno=(select deptno from dept where dname=’sales’))


4. Co-related Sub Query

A subquery that refers to a column of the main query with in the condition in it where clauses is call it is called co-related sub query.

Eg

Find highest salary emp of departmentwise?
=>
Select * from emp e where sal=(select max(sal) from emp s where s.deptno=e.deptno)

Difference between normal subquery & co-related

A normal subquery is executed only one where as co related sub query is executed multiple time for every row of subquery.

Eg

Find the emp whose salary is more than the avg salary of same dept in which that emp is working.

Select * from emp e where sal>(select avg(sal) from emp s where s.deptno=e.deptno)

If you have any query then frankly leave mail me [email protected]