--- title: "07 — Joins, Subqueries, and CTEs: Combining Tables" uid: joins-subqueries-ctes tags: ["roadmap:postgresql-dba", "subqueries", "recursive", "postgresql", "lateral", "cte", "joins", "set-operations"] excerpt: "Three families of data-combining queries: joins stitch rows side-by-side on a condition, subqueries nest one query inside another, CTEs name intermediate results so queries read top-to-bottom." date: 2026-08-13T03:27:52+0000 source: https://www.aveshina.my.id/en/blog/joins-subqueries-ctes --- 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: ```figure ``` 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](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](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](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/](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](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](https://www.postgresql.org/docs/current/queries-union.html) ```quiz Q: A LEFT JOIN keeps which rows? - Only rows that match in both tables - Every row from the left table, with NULLs for unmatched right-table columns correct: 1 explain: LEFT JOIN preserves all left rows; where there's no right-table match, the right columns come back as NULL. That's why it powers the "users with no orders" pattern when paired with WHERE right.id IS NULL. Q: What distinguishes a recursive CTE from a regular CTE? - A recursive CTE can be used in UPDATE; a regular one cannot - A recursive CTE refers to itself, with a base case UNION ALL a recursive case, iterating until termination correct: 1 explain: Recursive CTEs self-reference. A non-recursive seed UNION ALLs a recursive arm that selects from the CTE itself, walking trees or graphs until no new rows are produced. Q: What does UNION ALL do that UNION does not? - Deduplicate the combined rows - Keep duplicate rows in the combined result, avoiding an expensive sort correct: 1 explain: UNION removes duplicates (which requires sorting/hashing); UNION ALL simply concatenates. ALL is faster and preferred unless deduplication is actually needed. Q: LATERAL allows a subquery in the FROM clause to… - run independently of the rest of the query - reference columns from tables listed earlier in the FROM clause correct: 1 explain: LATERAL enables per-row correlation in FROM. It's the clean way to express "for each row in A, compute something from B" — like a top-N-per-group. Q: Why would you lift nested subqueries into named CTEs? - CTEs always execute faster than nested subqueries - It makes a long query read top-to-bottom as a pipeline of named steps correct: 1 explain: CTEs are primarily a readability tool — a chain of named results. Postgres may inline them; the speed is comparable. The win is clarity for the next reader. ```