Multi row Subqueries in SQL Server – SQL Server Tutorial

Multi Row Sub-Queries :–

• If inner query returns more than one value,then the sub query is called “multirow sub query”

Syntax :- Select  <collist>from<tabname> where colname operation (select statement)

• Here operation must be in, Not in, Any, All, Exists, not exists

Example  :- Display employee records whos job=job of Smith(or) job of blake.

Select * from emp where job in (select job from emp where ename=’smith’ or Ename=’BLAKE’)

Display department which is empty

Select deptno from dept where deptno not in (select deptno from emp)

Operators :

Any Operator in SQL Server :–

Used to compare any of the values in given list.

• x>any(1000,2,000,3000)  =>min
• x<any(1000,2000,3000)   =<max

All Operator in SQL Server :–

Used to compare all the values of given list.

• x  > all (1000,2000,3000)     > max
• x  < all (1000,2000,3000)     = min

Display employee records who are earning morethan all clerks.

• Select * from emp where sal > all (select sal from emp where job=’clerk’)
• Select * from emp where sal >any(select sal from emp where job =’clerk’)