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