SQL Subqueries

A subquery is a complete SELECT statement nested inside another SQL statement, used to compute an intermediate result that the outer query then filters, joins against, or selects from. Subqueries let you express multi-step logic — "find the average, then find rows above it" — in a single SQL statement.

Why it matters

Some questions need the answer to another question first: who earns more than the average, which customers have ordered a particular product. A subquery puts one query inside another to express that directly. Knowing when a subquery is the clearest tool, and when a join does the same job better, is part of writing good SQL.

Scalar Subqueries

A scalar subquery returns exactly one row and one column — a single value — and can be used anywhere a single value is expected, such as in a comparison inside WHERE or as a computed column in SELECT. For example, comparing a salary against the company-wide average salary computed by a nested SELECT AVG(salary).

Correlated Subqueries

A correlated subquery references a column from the outer query, so it is logically re-evaluated once for each row the outer query processes, rather than computed just once. This makes it possible to express per-row comparisons, such as "find employees earning more than the average salary in their own department" (as opposed to the company-wide average).

Subquery in WHERE vs. Subquery in FROM

A subquery in WHERE (often with IN, EXISTS, or a comparison operator) filters which outer rows qualify. A subquery in FROM (sometimes called a derived table or inline view) is treated as a temporary, named table that the outer query can select from and join against just like any real table — useful for pre-aggregating or pre-filtering data before the outer query works with it.

Examples

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Returns 1 row: ('Asha Rao', 72000) — assuming the overall average salary is 65000.

SELECT dept_summary.department, dept_summary.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_summary
WHERE dept_summary.avg_salary > 60000;

Returns 1 row: ('Engineering', 68000.00)

Exercise

Return the names of the employees who earn more than the average salary of all employees.

Expected output: name then Ravi

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
INSERT INTO employees (id, name, salary) VALUES
  (1, 'Asha', 50000),
  (2, 'Ravi', 60000),
  (3, 'Zoya', 40000);

-- TODO: names of employees earning more than the average salary

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
INSERT INTO employees (id, name, salary) VALUES
  (1, 'Asha', 50000),
  (2, 'Ravi', 60000),
  (3, 'Zoya', 40000);

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Quick quiz

Interview questions