SQL Subqueries: Correlated, Non-Correlated, EXISTS, and IN

Non-correlated subqueries

A non-correlated subquery executes once and its result is used by the outer query.

-- Employees who earn above average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- In FROM clause (derived table)
SELECT dept, avg_sal
FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) AS dept_averages
WHERE avg_sal > 80000;

Correlated subqueries

A correlated subquery references the outer query — it executes once per row in the outer query.

-- Find the highest-paid employee in each department
SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department  -- references outer query
);

IN and NOT IN

IN checks if a value exists in a list or subquery result. NOT IN returns rows where the value doesn't match any in the list.

-- Employees in departments with avg salary > 80k
SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE avg_salary > 80000
);

-- TRAP: NOT IN with NULL
SELECT name FROM employees
WHERE department_id NOT IN (1, 2, NULL);
-- Returns 0 rows! Any comparison with NULL returns NULL.

-- Safe alternative: NOT EXISTS
SELECT name FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM dept_exclusions de
    WHERE de.dept_id = e.department_id
);

EXISTS and NOT EXISTS

EXISTS returns true if the subquery returns at least one row. It's often more efficient than IN for existence checks.

-- Managers who have at least one direct report
SELECT DISTINCT m.name
FROM employees m
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.manager_id = m.id
);

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

Exam tip

The NOT IN + NULL trap is the most common subquery exam question. If any value in a NOT IN list is NULL, the whole query returns 0 rows. Always use NOT EXISTS instead of NOT IN when the subquery could return NULLs.