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’)