Subqueries for temp results

Concepts covered: Membership Subqueries, Scalar Subquery

A subquery is a SELECT nested inside another statement, and it predates the WITH clause by 2 decades of SQL. It appears in 3 positions, and the position decides what shape its result must have. In a comparison it must return 1 value. In an IN list it returns 1 column of any length. In FROM it returns a whole table, which the outer query reads under an alias. All 3 run to completion before the clause that contains them uses the result, exactly as a CTE does.

A scalar subquery returns one value

A parenthesised subquery that returns 1 row and 1 column can stand anywhere a single value can: compared in WHERE, or as a column in SELECT. The classic use is a comparison against an aggregate of the same table, which a plain WHERE can't write because the aggregate does not exist yet when WHERE runs. The subquery computes it in its own pass first.

SELECT id, user_id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders)

Across the 6 orders the average is 50.0, so the inner query returns that single number and the outer WHERE becomes total > 50.0, keeping orders 1002 (75), 1005 (60) and 1006 (90). If the inner query returned 2 rows, the comparison would be between 1 value and 2, and the engine raises an error rather than guessing which to use. The blank below is the same construction over the practice database.

An IN subquery returns a list

IN (subquery) tests a value against every row of a 1-column result. It's the inline form of the active-users filter from section 0: the inner query lists the ids of active users, and the outer WHERE keeps orders whose user_id appears in that list. The animation runs it over the same users and orders rows the CTE examples used.

SELECT id, user_id, total
FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE active = 1
)

The inner query returns user ids 98, 101 and 103. Orders 1001 and 1003 match, as do 1005 and 1006, so 4 of the 6 survive. Order 1002 belongs to Ben (99, inactive) and 1004 to Dev (102, inactive), so both drop. IN is safe with NULLs in the list; its negation NOT IN returns no rows at all if the list holds even 1 NULL, a trap the three-valued logic lesson shows on rows.

SELECT id, user_id, total,
       (SELECT AVG(total) FROM orders) AS avg_total
FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 1)
ORDER BY id

Both forms sit in one query here. The scalar subquery in the SELECT list is evaluated once and its value, 50.0, is repeated on all 4 output rows, while the IN subquery narrows the rows. Change the inner filter to active = 0 and the result flips to the 2 orders from Ben and Dev, with avg_total unchanged, because the scalar subquery reads all 6 orders regardless of the outer filter.

A derived table in FROM

A subquery in FROM is a table with no name, so SQL requires an alias for it. Everything an outer query can do to a real table it can do to the derived table: filter it, sort it, join it. This is how a filter on an aggregate was written before HAVING and WITH were common, and it still reads well for a single stage.

SELECT user_id, spent
FROM (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
) AS t
WHERE spent > 50
ORDER BY spent DESC

The inner query folds 6 orders into 5 per-user rows, the alias t names that 5-row set, and the outer WHERE spent > 50 keeps 3 of them. The outer WHERE can reference spent because, to the outer query, spent is a stored column of t: it was computed in the inner query's own SELECT step, which finished before the outer FROM began.

Subquery or CTE

The 2 forms always return identical rows, and their plans are often the same but not always. A subquery is planned inline with the query around it. A CTE may be materialized instead: PostgreSQL 12 and later do this for a CTE referenced twice, as section 0 described, and PostgreSQL before 12 materialized every CTE, so a filter in the outer query was never pushed into it. The choice is mostly about reading. One nested filter in WHERE reads better inline than as a 3-line block above the query; 2 stages, or 1 stage read twice, read better as CTEs. A subquery that references a column of the outer query is correlated, re-evaluated once per outer row, and belongs to the advanced tier of this track.

• In a comparison: returns exactly 1 value, or the engine errors
• In an IN list: returns 1 column, any number of rows
• In FROM: returns a table, and must carry an alias

About this lesson

This section is part of the Query Structure: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.