SQL subqueries and CTEs — the one-row trap

Start with the basics

A subquery is a query written inside another query — a lookup in the SELECT list, a filter in the WHERE, or a source table in the FROM.

A common table expression, or CTE, is the same idea with a name: you define it with a WITH clause and then use that name like a table — mostly for readability, so a long query reads top to bottom.

Subqueries and CTEs let you build complex logic in readable pieces. The traps are about how many rows a piece returns, and when the database runs it.

One that works

WITH big AS (
  SELECT id, amount FROM orders WHERE amount > 100
)
SELECT COUNT(*) AS n FROM big;

The CTE named 'big' is a subquery with a name; the outer query counts its rows — two orders over 100.

A subquery is a query nested inside another, and a CTE is just a subquery given a name.

When to use it

• ·Reach for a subquery when one query needs the result of another — a lookup, a filter, or a derived table.
• ·Use a CTE to name that subquery when the query gets long or you reuse the piece.
• ·Use a recursive CTE for hierarchies like org charts or category trees.

That one rule — a single-value subquery must return exactly one row — is fine until the data grows. The day a lookup returns a second row, the query that passed every test errors in production. Here is how.

See it happen

The lookup that passes dev and dies in prod

You look up each item's price with a subquery in the SELECT list. Today every SKU has exactly one price, so it works — and both versions return the same rows now. The difference shows up later.

SELECT id,
  (SELECT cents FROM prices
   WHERE prices.sku = items.sku) AS price
FROM items
ORDER BY id;

SELECT i.id, p.cents AS price
FROM items i
JOIN prices p ON p.sku = i.sku
ORDER BY i.id;

! It reads correctly today — but the day a SKU gets a second price row, the scalar subquery errors in production; a join on the key stays safe.

The rest of the family

More ways a subquery surprises you

Self-check

How to spot it in your own SQL

• →A query that worked on sample data errors once the real data grows.
• →You put a lookup inside the SELECT list as a subquery.
• →You rewrote a subquery as a join “for speed” and the row count changed.
• →A query is slow and has a subquery that references the outer row.

What people miss

Subqueries and CTEs are where “it works on my machine” comes from — a single-value subquery that returns one row today and two tomorrow, a rewrite-for-speed that quietly drops rows. It passes review and breaks in production.

Go deeper