Correlated SubQueries Examples

Correlated SubQueries Examples

Correlated sub query is used for row by row processing. The sub query is executed for each row of the main query.

1. Write a query to find the highest earning employee in each department?

SELECT DEPARTMENT_ID,

EMPLOYEE_ID,

SALARY

FROM EMPLOYEES E_0

WHERE 1 =

(

SELECT COUNT(DISTINCT SALARY)

FROM EMPLOYEES E_I

WHERE E_O.DEPARTMENT_ID = E_I.DEPARTMENT_ID

AND E_O.SALARY <= E_I.SALARY

)

2. Write a query to list the department names which have at lease one employee?

SELECT DEPARTMENT_ID,

DEPARTMENT_NAME

FROM DEPARTMENTS D

WHERE EXISTS

(

SELECT 1

FROM EMPLOYEES E

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

3. Write a query to find the departments which do not have employees at all?

SELECT DEPARTMENT_ID,

DEPARTMENT_NAME

FROM DEPARTMENTS D

WHERE NOT EXISTS

(

SELECT 1

FROM EMPLOYEES E

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)