EXISTS and NOT EXISTS

EXISTS checks if a subquery returns any rows. It returns TRUE or FALSE.

Find departments that have employees:

SELECT d.name FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.id
);

NOT EXISTS finds the opposite - departments with no employees:

SELECT d.name FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.id
);

EXISTS vs IN:

• EXISTS stops as soon as it finds one match (faster for large datasets)
• IN checks the entire list
• EXISTS handles NULLs correctly
• Use EXISTS for correlated checks, IN for simple value lists

SELECT 1 is a convention - EXISTS only cares if rows exist, not what columns they contain.