Chapter 10 — Key Takeaways

The big idea

Set operations stack query results by row (vs. joins, which widen by column) — the SQL form of Chapter 4's set algebra. They're the cleanest way to say "this plus that," "in both," and "in one but not the other."

The operators

Operator Algebra Returns Dedup?
UNION ∪ rows in either query yes
UNION ALL ∪ (bag) rows in either, duplicates kept no (faster)
INTERSECT ∩ rows in both yes (ALL keeps mult.)
EXCEPT − rows in first not second yes (ALL keeps mult.)

Union compatibility

  • Same number of columns, same order, compatible types.
  • Output column names come from the first query.

The crucial choice: UNION vs UNION ALL

  • UNION removes duplicate rows; UNION ALL keeps them.
  • Default to UNION ALL — it's faster (no dedup) and avoids silently merging distinct-but-identical rows. Use UNION only when you truly want duplicate removal. (Case Study 1: a UNION ate real settlements.)
  • Carry an id / source tag when combining sources, so rows are distinguishable and traceable.

INTERSECT & EXCEPT

  • INTERSECT = "in both" (great for membership checks over ids).
  • EXCEPT = set-difference anti-join ("products never ordered," "orders that didn't migrate").
  • Both compare whole rows → selecting all key columns catches value-level differences (Case Study 2: reconciliation).
  • For differences/intersections that need extra columns or conditions, prefer NOT EXISTS/EXISTS (Ch. 9) — more flexible than EXCEPT/INTERSECT.

Mechanics

  • One ORDER BY at the very end sorts the combined result.
  • INTERSECT binds tighter than UNION/EXCEPT — parenthesize mixed operators.

Power use: reconciliation

Two EXCEPTs (A−B and B−A) + an INTERSECT count = a provable comparison of two data sets (migrations, audits, dedup checks). Empty EXCEPTs = faithful copy.

You can now…

  • ☐ Combine results with UNION/UNION ALL, INTERSECT, EXCEPT.
  • ☐ Satisfy union compatibility.
  • ☐ Choose UNION vs UNION ALL deliberately.
  • ☐ Use EXCEPT/INTERSECT for differences, membership, and reconciliation.
  • ☐ Order and parenthesize combined queries correctly.

Looking ahead

Chapter 11 — CTEs & Recursive Queries. The WITH clause names the derived tables you've been nesting (readable, reusable), and recursive CTEs traverse hierarchies — Mercado's category tree and employee org chart.

One sentence to carry forward: Default to UNION ALL, reach for EXCEPT/INTERSECT to make set questions read like English, and remember two EXCEPTs reconcile any two data sets exhaustively.