SQL Subquery Patterns — Scalar, Table, and Correlated

Subquery Patterns in SQL

A subquery is a SELECT statement nested inside another query. Understanding the three main types helps you choose the right pattern for each situation.

1. Scalar Subquery

Returns a single value. Can be used wherever a single value is expected.

-- Employees earning above the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

2. Table Subquery (Derived Table)

Returns a result set used as a virtual table in the FROM clause.

SELECT dept_name, avg_salary
FROM (
  SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 70000;

3. Correlated Subquery

References a column from the outer query. Executes once per row of the outer query.

-- Employees earning above their department's average
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

EXISTS and NOT EXISTS

-- Departments that have at least one employee
SELECT d.name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.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
);

IN vs EXISTS

• IN materializes the subquery result, then checks membership. Good when the subquery returns a small set.
• EXISTS short-circuits as soon as a match is found. Generally faster for large outer tables with a correlated check.

When to Use a CTE Instead

If your subquery is complex or referenced multiple times, a Common Table Expression (WITH) is usually more readable and maintainable. See the CTE topic for details.