Sub Query

SUB QUERY

Query inside a Query. (Select statement in select statement) The advantage of sub query is that reduces Network Traffic.

Table Employees

ENO	ENAME	DESIGNATION	SAL	DEPTNO
1	MANU	MANAGER	44000	10
2	VIVEK	ACCOUNTANT	14000	20
3	ANU	CLEARK	4000	20
4	BIJU	PROGRAMMER	24000	30
5	RENU	MANAGER	30000	20
6	JOY	CLEARK	3500	10
7	UNNI	MANAGER	40000	30
8	VANI	GENERAL MANAGER	40000	20

SELECT * FROM EMPLOYEES WHERE DGN= (SELECT DGN FROM EMPLOYEES WHERE ENAME=JOY)

Note: SELECT * FROM EMPLOYEES WHERE DGN= is Main Query or Outer Query

(SELECT DGN FROM EMPLOYEES WHERE ENAME = ANU) is Sub query or Inner Query.

Multiple Sub Query

SELECT * FROM EMPLOYEES WHERE DGN IN (SELECT DGN FROM EMPLOYEES WHERE ENAME=’MANU’) AND SAL= (SELECT SAL FROM EMPLOYEES WHERE ENAME = ‘VANI’)

Multi Level Sub Query- Sub Query inside a sub query is multilevel sub query.

Correlated Sub Query

Is a type of sub Query which is executed once for each row processed by the main Query. The execution of the Sub Query co-related to the candidate row of the main Query.

SELECT * FROM EMPLOYEES AS E WHERE E.SAL>(SELECT AVG(SAL) FROM EMPLOYEES WHERE E.DEPTNO=DEPTNO)

Check the Average

SELECT DEPTNO, AVG(SAL) FROM EMPLOYEES GROUP BY DEPTNO.