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.