Hamropedia

Phase 4 of 8 · 5 lessons

Subqueries, CTEs and set operations

Use the result of one query inside another, break hard questions into named steps with WITH, walk trees with recursion, and stack results with UNION.

A query's result is a table, so it can feed another query. That one idea turns hard questions ("customers who spend more than the average customer") into a chain of easy ones — and CTEs let you write that chain top to bottom, the way you would explain it.

By Manoj Kumar Thapa

AI Software Engineer ·

0 of 5 done

Lessons, in order

  1. 4.1

    Subqueries: a query inside a query

    Must know

    A subquery is a SELECT inside another query, in brackets: it can return one value (to compare with), a list (for IN), or a whole table (to select FROM).
  2. 4.2

    Correlated subqueries, EXISTS, and the NOT IN trap

    Must know

    A correlated subquery refers to a column of the outer row, so it is worked out once per outer row; EXISTS asks "does the subquery return any row at all?", which makes it the safe way to test for a match.
  3. 4.3

    CTEs: naming each step with WITH

    Must know

    A CTE (common table expression) is a named, temporary result written at the top of a query with WITH name AS (…), which later parts of the query can use like a table.
  4. 4.4

    Recursive CTEs: walking trees and filling in dates

    Should know

    A recursive CTE refers to itself: an anchor query gives the starting rows, and a recursive part, joined with UNION ALL, keeps adding rows from the previous round until no new rows appear.
  5. 4.5

    UNION, UNION ALL, INTERSECT and EXCEPT

    Should know

    Set operators stack the results of two queries: UNION ALL keeps every row, UNION removes duplicates, INTERSECT keeps rows found in both, and EXCEPT keeps rows in the first but not the second.