Know if you're actually ready. Take the SQL quiz → get your AI readiness report.Take the free test →

Study guide · SQL

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

Subqueries unlock powerful data retrieval patterns. Here's what SQL exams test — correlated vs non-correlated, EXISTS, and IN.

Examifyr·2026·6 min read

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
);

NoteCorrelated subqueries can be slow on large tables because they run once per row. Often replaceable with window functions for better performance.

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
);

NoteNOT IN with a subquery that can return NULL rows will return an empty result. Always use NOT EXISTS or filter NULLs explicitly.

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
);

NoteEXISTS stops at the first matching row — it doesn't scan the whole subquery result. SELECT 1 is a convention; the selected value doesn't matter.

Exam tipThe 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.

Want a person to help?

Struggling with SQL? Work 1:1 with a vetted SQL tutor on Wyzant. (affiliate link)

Find a SQL tutor →

← All SQL guides