UNION, INTERSECT & EXCEPT — SQL Course

What you'll learn

Combine compatible result sets and reason about duplicate elimination, ALL variants, column alignment, and final ordering. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply UNION to a realistic data question
Apply UNION ALL to a realistic data question
Apply INTERSECT to a realistic data question
Apply EXCEPT to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
UNION ALL	Concatenation preserving duplicates	Prefer when inputs are disjoint or duplicate counts matter
UNION	Concatenation followed by duplicate elimination	Pay its cost only when set semantics require uniqueness
Column alignment	Inputs combine by position with compatible types	Name and cast columns deliberately in every branch

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the set-combination query question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Compare customer populations

EXCEPT answers which active customers have never completed a purchase.

prospects_without_purchase.sql

SELECT customer_id
FROM marketing.active_subscribers
EXCEPT
SELECT customer_id
FROM sales.orders
WHERE status = 'paid';

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Set operations align columns by position, not alias. Reversing two compatible columns can produce plausible but corrupt output.

Independent workshop

Build a review-ready set-combination query lab against the course commerce dataset.

Your finished workshop must include:

UNION
UNION ALL
INTERSECT
EXCEPT
Type compatibility
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

UNION ALL: Prefer when inputs are disjoint or duplicate counts matter
UNION: Pay its cost only when set semantics require uniqueness
Column alignment: Name and cast columns deliberately in every branch

Quick check

1. Which rule best applies to UNION ALL?

2. Which rule best applies to UNION?

3. Which rule best applies to Column alignment?

Next: Phase Project: Sales Reporting Database