SQL Subqueries

A subquery (also called an inner query or nested query) is a SELECT statement embedded inside another SQL statement. Instead of running two separate queries and manually combining the results, you let the database engine run the inner query first and feed its output directly into the outer query. Subqueries let you answer questions that a single flat query cannot — “employees who earn more than the company average”, “customers who have never placed an order”, “the top-selling product per category” — without writing application-side loops.

This lesson covers every place a subquery can appear, how the engine actually evaluates them (including the expensive correlated case), the classic mistakes that silently return wrong results, and how to rewrite slow subqueries as joins.

Overview: How Subqueries Work

A subquery is just a normal SELECT wrapped in parentheses and placed somewhere another expression, table, or list of values would go. The database engine treats it as one of four things depending on where it sits:

Scalar subquery — returns exactly one column and one row (or zero rows, which becomes NULL). Can be used anywhere a single value is expected: after =, >, <, in the SELECT list, or in WHERE/HAVING.
Multi-row (list) subquery — returns one column but many rows. Used with IN, NOT IN, ANY, or ALL.
Derived table (subquery in FROM) — returns a full result set that the outer query treats like a temporary, unnamed table. Must be given an alias.
Correlated subquery — references a column from the outer query, so it cannot be evaluated once up front. It is logically re-evaluated for every row the outer query considers, similar to a nested loop. EXISTS and NOT EXISTS almost always introduce a correlated subquery.

A crucial distinction is non-correlated vs. correlated. A non-correlated subquery (like WHERE salary > (SELECT AVG(salary) FROM employees)) has no dependency on the outer row, so the engine can compute it once, cache the result, and reuse it for every row of the outer query — cheap. A correlated subquery depends on the current outer row (for example WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)), so conceptually the engine runs the inner query once per outer row. In practice, modern query optimizers (including SQLite’s) often rewrite correlated subqueries into semi-joins or anti-joins internally when possible, using indexes on the correlated column to avoid a literal per-row scan — but you should still assume a correlated subquery is more expensive than an equivalent join unless you’ve checked the plan with EXPLAIN QUERY PLAN.

Syntax

The general shape of a subquery is a complete SELECT statement placed inside parentheses wherever an outer clause expects a value, list, or table:

SELECT column_list
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM another_table
    WHERE condition
);

Location	Returns	Typical operators
`WHERE` / `HAVING`	Scalar (1 row, 1 col)	`=`, `<`, `>`, `<=`, `>=`, `<>`
`WHERE` / `HAVING`	List (many rows, 1 col)	`IN`, `NOT IN`, `ANY`, `ALL`
`WHERE` / `HAVING`	Row existence only	`EXISTS`, `NOT EXISTS`
`FROM`	Full result set (derived table)	must have an alias, e.g. `AS t`
`SELECT` list	Scalar per outer row	adds a computed column

Every subquery must be a syntactically complete SELECT; you cannot use ORDER BY inside a scalar or list subquery in standard SQL unless paired with LIMIT (SQLite permits it, but relying on unordered subquery rows is fragile in general).

Examples

Example 1: Scalar subquery in WHERE

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
    (1, 'Alice', 'Engineering', 95000),
    (2, 'Bob', 'Engineering', 72000),
    (3, 'Carol', 'Sales', 68000),
    (4, 'Dave', 'Sales', 54000),
    (5, 'Eve', 'Marketing', 61000);

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

Result:

name	department	salary
Alice	Engineering	95000
Bob	Engineering	72000

The inner query computes the company-wide average salary (350000 / 5 = 70000) once. The outer query then keeps only the rows where salary exceeds that single number. Carol’s 68000 is below the average, so she is excluded even though she out-earns two other colleagues.

Example 2: Multi-row subquery with IN

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    country TEXT
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    amount REAL
);

INSERT INTO customers (customer_id, customer_name, country) VALUES
    (1, 'Acme Corp', 'USA'),
    (2, 'Globex', 'UK'),
    (3, 'Initech', 'Canada'),
    (4, 'Umbrella', 'USA');

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
    (101, 1, '2026-01-05', 250.00),
    (102, 1, '2026-02-10', 75.50),
    (103, 2, '2026-01-20', 430.00);

SELECT customer_name, country
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

Result:

customer_name	country
Acme Corp	USA
Globex	UK

The inner query returns the distinct-ish list of customer IDs that appear in orders (1, 1, 2). IN checks each outer row’s customer_id against that list. Initech and Umbrella never placed an order, so they are left out.

Example 3: Correlated subquery with EXISTS

SELECT c.customer_name, c.country
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > 400
);

(Using the same customers/orders tables and data as Example 2.)

Result:

customer_name	country
Globex	UK

This subquery is correlated because o.customer_id = c.customer_id ties the inner query to whichever outer row is currently being tested. For each customer, the engine checks whether at least one matching order over $400 exists. Only order 103 (amount 430, customer 2) qualifies, so only Globex is returned. EXISTS doesn’t care what columns the subquery selects — only whether it produces any row at all, which is why SELECT 1 is idiomatic and slightly cheaper than selecting real columns.

How It Works Step by Step (Under the Hood)

SQL’s logical processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. A subquery slots into whichever clause it’s written in and is conceptually resolved before that clause’s logic runs:

Non-correlated (Example 1): the engine evaluates (SELECT AVG(salary) FROM employees) exactly once, gets a single number (70000), substitutes it in place of the subquery, and the outer WHERE becomes a plain salary > 70000 comparison.
List subquery (Example 2): the inner query materializes a set of values {1, 1, 2}; IN is then just set membership testing against each outer row.
Correlated (Example 3): because the inner query mentions c.customer_id, it cannot be pre-computed. Logically, for every candidate row in customers, the engine substitutes that row’s customer_id into the subquery and re-runs it, stopping at the first match for EXISTS (short-circuit evaluation — it doesn’t need to count all matching orders, just confirm one exists). SQLite’s optimizer commonly turns this pattern into an efficient semi-join using an index on orders.customer_id rather than literally looping row by row, but the results are identical to the naive per-row model.

Subqueries can also appear in FROM (a derived table) and in the SELECT list (a per-row scalar):

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
    (1, 'Alice', 'Engineering', 95000),
    (2, 'Bob', 'Engineering', 72000),
    (3, 'Carol', 'Sales', 68000),
    (4, 'Dave', 'Sales', 54000),
    (5, 'Eve', 'Marketing', 61000);

SELECT department, department_avg
FROM (
    SELECT department, AVG(salary) AS department_avg
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE department_avg > 70000;

Result: a single row, Engineering | 83500.0. The derived table dept_stats is computed first (Engineering avg 83500, Sales avg 61000, Marketing avg 61000), and the outer query filters that already-aggregated result — something a plain HAVING could also do here, but a derived table lets you reuse or further join that aggregated set, which HAVING alone cannot.

SELECT name, department, salary,
       (SELECT ROUND(AVG(salary), 2) FROM employees) AS company_avg
FROM employees;

Result: 5 rows, each with the same company_avg value of 70000.0 appended — Alice/Engineering/95000, Bob/Engineering/72000, Carol/Sales/68000, Dave/Sales/54000, Eve/Marketing/61000, all showing company_avg = 70000.0. A scalar subquery in the SELECT list is re-evaluated conceptually for every output row, so use it for small, cheap calculations — not for anything that scans a large table, since it typically cannot benefit from a join’s single-pass execution.

Common Mistakes

Mistake 1: Using = with a subquery that can return multiple rows

-- Wrong: this subquery can return more than one distinct department
SELECT name
FROM employees
WHERE department = (
    SELECT department
    FROM employees
    WHERE salary > 65000
);

Employees earning more than 65000 span two departments (Engineering and Sales), so the inner query returns two rows. The = operator only accepts a single value, and the engine raises a runtime error (something like “sub-select returns more than one row”) instead of silently picking one. This is a genuine execution failure, not just bad style.

-- Corrected: use IN when the subquery can return multiple rows
SELECT name
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    WHERE salary > 65000
);

Result: Alice, Bob, Carol, Dave (4 rows) — everyone in Engineering or Sales, since those are the departments containing at least one employee above 65000. Eve (Marketing) is excluded. Note that Dave (54000) is included even though his own salary is below the threshold, because the filter is on department membership, not his individual salary.

Mistake 2: NOT IN silently returning nothing because of a NULL

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    country TEXT
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    amount REAL
);

INSERT INTO customers (customer_id, customer_name, country) VALUES
    (1, 'Acme Corp', 'USA'),
    (2, 'Globex', 'UK'),
    (3, 'Initech', 'Canada');

-- order 102 has no customer_id (e.g. a guest checkout)
INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
    (101, 1, '2026-01-05', 250.00),
    (102, NULL, '2026-01-06', 60.00);

-- Wrong: returns ZERO rows, even though Globex and Initech never ordered
SELECT customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Result: an empty result set. This runs without any error, which makes it especially dangerous. The inner query’s value list is {1, NULL}. SQL compares each customer against every value in that list with AND logic: 2 <> 1 is true, but 2 <> NULL is NULL (unknown) — and TRUE AND NULL is NULL, which is not TRUE, so the row is dropped. A single NULL anywhere in a NOT IN subquery poisons the entire comparison for every outer row.

-- Corrected: filter out NULLs, or use NOT EXISTS instead
SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Result: Globex, Initech (2 rows) — the correct answer. NOT EXISTS never has this problem because it tests row existence, not value equality against a list that might contain NULL.

Best Practices

Prefer EXISTS / NOT EXISTS over IN / NOT IN when the subquery’s column can contain NULL values — it sidesteps the three-valued-logic trap entirely.
When a correlated subquery just checks for related rows, try rewriting it as a JOIN (or semi-join) and compare the query plan with EXPLAIN QUERY PLAN; many engines optimize joins more aggressively than equivalent correlated subqueries.
Make sure a subquery used with =, <, >, or in the SELECT list can only ever return one row and one column — add aggregation (MAX, AVG, LIMIT 1 with an ORDER BY) if needed to guarantee that.
Give every derived table (FROM (SELECT ...)) a meaningful alias — it’s required by SQL syntax and makes the outer query far more readable.
Avoid scalar subqueries in the SELECT list over large tables; they are conceptually re-run per output row and usually lose to a JOIN plus GROUP BY for bulk calculations.
Test subqueries in isolation first — run the inner SELECT by itself to confirm it returns what you expect before wrapping it in the outer query.

Practice Exercises

Exercise 1: Using the employees table from Example 1, write a query that returns the names of employees whose salary is below the average salary of their own department (hint: this needs a correlated subquery, similar in shape to the department-MAX pattern but using AVG and <).
Exercise 2: Using the customers and orders tables from Example 2, write a query that lists every customer who has placed zero orders, using NOT EXISTS rather than NOT IN. Expected result: Initech and Umbrella.
Exercise 3: Rewrite the derived-table query from the “Under the Hood” section (departments with an average salary above 70000) so that it produces the same result using GROUP BY and HAVING instead of a subquery in FROM. Compare the two approaches — when would you still prefer the derived-table version?

Summary

A subquery is a complete SELECT nested inside another statement’s WHERE, HAVING, FROM, or SELECT clause.
Scalar subqueries return one value; list subqueries return many rows for IN/ANY/ALL; derived tables return a whole result set used in FROM.
Non-correlated subqueries are evaluated once; correlated subqueries reference an outer column and are logically re-evaluated per outer row, similar to a nested loop.
Using = with a subquery that can return multiple rows is a runtime error — use IN, ANY, or aggregate down to one row.
NOT IN silently returns no rows if its subquery’s result contains a NULL; prefer NOT EXISTS when NULLs are possible.
Many correlated subqueries can and should be rewritten as joins for better performance on large tables — check with EXPLAIN QUERY PLAN.

SQL Subqueries

Overview: How Subqueries Work

Syntax

Examples

Example 1: Scalar subquery in WHERE

Example 2: Multi-row subquery with IN

Example 3: Correlated subquery with EXISTS

How It Works Step by Step (Under the Hood)

Common Mistakes

Mistake 1: Using = with a subquery that can return multiple rows

Mistake 2: NOT IN silently returning nothing because of a NULL

Best Practices

Practice Exercises

Summary

Table of Contents