Co-related sub-queries :-
- In co-related sub-queries execution starts from outer query for every record of outer query table Inner query is executed once.
- In co-related sub-queries inner query also takes value from outer query.
Example :- Display employee records earning morethan avg salary of their dept
Select * from emp a where a.sal>(select avg(sal) from emp where deptno=a.deptno)
- Where → 1 A 5000 10 √ avg
- 2 B 4000 20 x 5000 > (4000)
- 3 C 3000 10 x 4000 > (4500)
- 4 D 5000 20 √ 3000 > (4000)
- 5 E 6000 30 x 5000 > (4500)
Display names of the employee earning max salaries in their department
Select ename,sal,deptno from emp X where x.sal=(select max (sal) from emp where deptno = x.deptno)
- In co-related sub-queries,we can use two operations
1) exists (sub query)
2) Non exists (sub query)
3) Exists non exists returns true/false.
- Exists returns true ,if sub query returns at-least one record.
- If sub query returns no records then exists returns false.
- Exists operation is used to check whether the record is present in the table(or) not.
- Display department list which are not empty.
Select * from dept d where exists (select * from emp where deptno=d.deptno).
- Display departments which are empty.
Select * from dept d where not exists(select * from emp where deptno=d.deptno)
SQL server recommands use exists, not exists instead of in & not in operator.
Display list of managers
Select * from emp x where exists (select * from emp where mgr=x.empno)
Example :–
Empno ename mgr
1 A —
2 B 1
3 C 1
4 D 2
Here A is manager for C & B
B is manager for D
C is not manager for any one similarly D
Difference between join and subquery :–
- Use sub queries we got data from one table & condition based on another table
- Use join operator to get data from multiple tables.