A subquery is a query nested inside another SQL query, whose result the outer query uses as a value, a list, or a table. Any time you catch yourself thinking “first I need to figure out X, then use it to find Y,” a subquery is one way to write that in a single statement.
The three shapes
Subqueries come in a few shapes, distinguished by what they hand back to the outer query. A scalar subquery returns exactly one value and can stand anywhere a value could — comparing each order to the overall average, say. A subquery can also return a list for the outer query’s predicate to test against, which is the classic IN pattern:
SELECT customer_id, amount
FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE plan = 'enterprise'
)
AND amount > (SELECT AVG(amount) FROM orders);
Here the IN subquery produces the set of enterprise customers, and the scalar subquery produces one number — the average order amount — that every row is compared against. A third shape, the derived table, puts a whole subquery in the FROM clause so the outer query can treat its result like a table.
The trickiest variant is the correlated subquery, which references a column from the outer query — for instance, comparing each order to the average for that customer rather than the global average. Conceptually it re-runs once per outer row, which is expressive but easy to make slow; modern optimizers often rewrite correlated subqueries into joins, but not always, so they’re the usual suspect when a nested query crawls.
Subquery, CTE, or join?
These three overlap a lot, and choosing is mostly about readability. A CTE is functionally a subquery you’ve named and lifted to the top of the statement — WITH enterprise_customers AS (...) — which reads top-down instead of inside-out and can be referenced more than once. Once a query nests two or more levels deep, converting the layers to CTEs almost always makes it clearer.
A join is often the better tool when you need columns from the other table, not just a membership test. IN (SELECT ...) answers “is this customer in the set?”; a join brings the customer’s name and plan along too. For pure existence checks, EXISTS is a common alternative to IN, and it handles nulls in the subquery’s result less surprisingly.
Subqueries in Metabase
Metabase leans on subqueries quietly. When you build a question in the query builder starting from a saved question or a model, Metabase wraps that starting point’s query as a nested query and layers your filters and summaries on top — a subquery you never had to write. In the native editor you write them yourself, and the same judgment applies: subqueries for quick one-level nesting, CTEs when the query grows, joins when you need the other table’s columns.
Related terms
Further reading
Put it to work
- Using common table expressions — Tutorial
- SQL cheat sheet — Cheat sheet
- Best practices for writing SQL queries — Tutorial
