Using the EXISTS and NOT EXISTS Operator

EXISTS

Find employees who have at least one person reporting to them.

SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
                               FROM employees
                               WHERE manager_id = outer.employee_id);
NOT EXISTS

Find all departments that do not have any employees.


SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                                        FROM employees
                                        WHERE department_id = d.department_id);