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.