07 — Joins, Subqueries, and CTEs: Combining Tables

Memorizing join syntaxes in isolation left me fumbling the moment data came from more than one place. The framing that organized them: there are three families — joins stitch rows side-by-side on a condition, subqueries nest one query inside another, and CTEs name intermediate results so a query reads top-to-bottom [1][2][3]. They overlap heavily (a subquery can often be rewritten as a join, a CTE is a named subquery), so the choice is about readability and the occasional performance edge, not capability.

Joins: stitching rows side-by-side

A join combines rows from two tables where a condition matches [1]. The type controls what happens to rows that don't match:

The way of thinking: the join condition (ON users.id = orders.user_id) decides which rows line up; the join type decides which unmatched rows survive, with NULL filling the gaps. INNER keeps only matched rows. LEFT keeps every row from the left table even if there's no match on the right. RIGHT is the mirror. FULL OUTER keeps everything from both sides.

SELECT u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

This returns every user, with order_id NULL for users who haven't ordered. That's the classic "find users with no orders" pattern: add WHERE o.id IS NULL.

Subqueries: nesting queries

A subquery is a query inside another query [2]. It can appear in WHERE (a scalar or IN filter), in FROM (a derived table), or in SELECT (a correlated scalar). The shape I use most is the IN subquery:

SELECT email FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total_cents > 10000);

A correlated subquery references the outer query, re-evaluating per row. A lateral subquery (covered below) is a more powerful form of correlation.

CTEs: naming intermediate results

A Common Table Expression is a named subquery declared with WITH that the main query can reference [3]. The win is readability — a long query becomes a sequence of named steps instead of nested subqueries:

WITH big_orders AS (
  SELECT user_id, COUNT(*) AS n FROM orders
  WHERE total_cents > 10000
  GROUP BY user_id
)
SELECT u.email, b.n
FROM users u
JOIN big_orders b ON b.user_id = u.id
WHERE b.n >= 3;

big_orders reads like a temporary view that exists only for this query. CTEs can chain (each referencing the previous), and UNION ALL combines multiple queries into one result.

Recursive CTEs: tree and graph traversal

A recursive CTE refers to itself, iterating until a termination condition [4]. The pattern is a base case UNION ALL-ed with a recursive case that selects from the CTE itself:

WITH RECURSIVE chain AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, chain.depth + 1
  FROM categories c
  JOIN chain ON c.parent_id = chain.id
)
SELECT * FROM chain ORDER BY depth;

This walks a tree (categories with a parent_id) from the root down. The base case seeds it with roots; the recursive case joins children to the previous level; UNION ALL accumulates. The same shape handles org charts, threaded comments, and graph reachability. The warning from the roadmap is real: a missing or wrong termination condition means an infinite loop.

LATERAL: correlated subqueries in FROM

LATERAL lets a subquery in FROM reference columns from tables listed before it [5]. It's the clean way to express "for each row in A, run this subquery against B":

SELECT u.email, recent.id AS latest_order
FROM users u
LEFT JOIN LATERAL (
  SELECT id FROM orders WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 1
) recent ON true;

Without LATERAL, this would be a correlated subquery in SELECT or a window-function hack (computing a value per row by looking across a group of related rows). LATERAL makes the intent explicit and often plans better.

Set operations: UNION, INTERSECT, EXCEPT

When I need to combine whole result sets rather than rows, the set operators apply [6]:

• UNION / UNION ALL — concatenate two result sets (UNION deduplicates; UNION ALL is faster and keeps duplicates).
• INTERSECT — keep rows present in both.
• EXCEPT — keep rows in the first but not the second.

SELECT email FROM users
UNION
SELECT email FROM newsletter_signups;

Both sides must return the same number and compatible types of columns.

How I use this

The habit is reach-for-CTE for readability, reach-for-joins for the actual stitching. When a query grows past two levels of nesting, I lift the inner pieces into named CTEs so the query reads top-to-bottom as a pipeline. For hierarchical data, the recursive CTE is the tool, with a careful termination check. For "top-N per group" patterns, LATERAL is cleaner than window functions. And I prefer UNION ALL over UNION unless I specifically need deduplication, because the dedup sort is expensive and usually unnecessary. The capability overlaps; the choice is which form makes the intent clearest to the next reader.

References

[1] PostgreSQL Global Development Group, "Joins Between Tables," 2024. [Online]. Available: https://www.postgresql.org/docs/current/tutorial-join.html

[2] PostgreSQL Global Development Group, "Subquery Expressions," 2024. [Online]. Available: https://www.postgresql.org/docs/current/functions-subquery.html

[3] PostgreSQL Global Development Group, "WITH Queries (Common Table Expressions)," 2024. [Online]. Available: https://www.postgresql.org/docs/current/queries-with.html

[4] PostgreSQLTutorial.com, "PostgreSQL Recursive Query," 2024. [Online]. Available: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-recursive-query/

[5] PopSQL, "How to use lateral joins in PostgreSQL," 2024. [Online]. Available: https://popsql.com/learn-sql/postgresql/how-to-use-lateral-joins-in-postgresql

[6] PostgreSQL Global Development Group, "Combining Queries (UNION, INTERSECT, EXCEPT)," 2024. [Online]. Available: https://www.postgresql.org/docs/current/queries-union.html