MODULE 02 • SESSION 23 • TOPIC 27

Set Operations: UNION, INTERSECT & EXCEPT

Vertical Stream Concatenation, Mathematical Venn Algebra & Omnichannel Feed Synthesis

23

Interactive Scenes

4

Visual Studios

4

Arena Challenges

8

Interview Scenarios

Core Focus: Vertical Stacking, UNION ALL Performance, ANSI Set Contracts & EXCEPT Churn

✓ Verified on PostgreSQL 18

Welcome to Session 23

Today we transition from horizontal joins to vertical set concatenation. We learn UNION, UNION ALL, INTERSECT, and EXCEPT.

Today’s Agenda — Set Operations Architecture

From vertical tuple stacking and UNION stream performance to Venn cohort analysis and ANSI schema contracts.

Pillar 01

🐦 Set Algebra Foundations

01 Vertical Pipeline Stacking

02 ANSI SQL Positional Contract

03 Implicit Type Coercion Rules

Pillar 02

⚖ UNION vs UNION ALL

04 UNION ALL 10× Stream Concat

05 Bare UNION Sort-Unique Cost

06 Memory Spills & HashAgg Overhead

Pillar 03

🔄 Cohort Intersections

07 INTERSECT Cross-Channel Cohorts

08 EXCEPT Lapsed Account Churn

09 NULL Equivalence in Set Algebra

Pillar 04

🎮 Practice & Mastery

10 Execution Plan Operator Inspection

11 4-Level Practice Arena Drills

12 8 Senior Interview Scenarios

💡

Why this matters: Joins expand result sets horizontally by linking foreign keys across different entities. Set operations stack result sets vertically into a single unified stream. Master UNION ALL for high-throughput stream concatenation, UNION for distinct deduplication, and INTERSECT / EXCEPT for mathematical cohort discovery.

Where We Left Off: Horizontal Joins to Vertical Stacking

Transitioning from linking tables side-by-side to concatenating query streams into unified pipelines

👥 SESSION 22 • COMPLETED

Intra-Table Mirroring

-- Session 22: Self-join peer matching

SELECT

e1.store_id,

e1.first_name AS emp_1, e2.first_name AS emp_2

FROM stores.employees e1

JOIN stores.employees e2

ON e1.store_id = e2.store_id

AND e1.employee_id < e2.employee_id;

Session 22 mastered intra-table queries using dual aliases (e1, e2) and strict inequality (<) to eliminate self-matches and reverse mirror pairs. But all joins still link rows horizontally side-by-side.

✓ Horizontal relational joins mastered; Vertical set stacking awaits

🐦 SESSION 23 • THE MISSION

Vertical Pipeline Synthesis

-- Session 23: Vertical set stacking

SELECT

customer_id AS id, first_name AS name,

'Customer' AS entity_type

FROM customers.customers

UNION ALL

SELECT

supplier_id, supplier_name, 'Supplier'

FROM products.suppliers;

Stack independent query outputs vertically into a single stream. Learn the 10x performance advantage of UNION ALL over UNION, and use INTERSECT and EXCEPT to isolate cohort churn.

🐦 Today: UNION ALL, Bare UNION, INTERSECT, EXCEPT, Set Schema Contracts

💡

The Core Distinction: Joins correlate rows using relational keys and grow wider (horizontal expansion). Set operations append rows using positional type compatibility and grow taller (vertical concatenation). Column names are inherited solely from the first SELECT query!

💸

The Double Cashback Incident UNION ALL • 3,686 CUSTOMERS PAID TWICE

NAMES MADE UP • NUMBERS REAL

₹3,68,600 Paid Twice

27,145 → 23,459 Payouts → Real Customers

3,686 On Both Lists

STEP 01 • THE INCIDENT

finance-ops 08:45 AM

“Festive offer: ₹100 cashback to every customer who paid by UPI or by Credit Card. Marketing stacked the UPI list (13,527 customers) and the Card list (13,618) with UNION ALL and paid 27,145 times. Some customers got ₹200. Who, and how much did we lose?”

UNION ALL keeps every row, so a customer on both lists was paid once per list.

STEP 02 • ROOT CAUSES

⚠ UNION ALL Keeps Duplicates

27,145 Payouts

UNION ALL just puts list 2 under list 1. Nothing is removed, so a customer who used both UPI and a Card appears twice.

🔍 INTERSECT Finds the Overlap

3,686 on Both Lists

UPI list INTERSECT Card list returns the 3,686 customers in both. 3,686 × ₹100 = ₹3,68,600 paid twice.

✅ UNION Keeps One Row Each

23,459 Customers

UNION removes duplicate rows (it has to sort or hash them, so it is slower than UNION ALL). One row = one customer = one payout.

STEP 03 • THE FIX

UNION

💸 One Payout per Customer

-- 2 = UPI, 3 = Credit Card (finance.payment_modes)
SELECT cust_id
FROM sales.orders
WHERE payment_mode_id = 2
UNION
SELECT cust_id
FROM sales.orders
WHERE payment_mode_id = 3
ORDER BY cust_id
LIMIT 10;
-- without LIMIT: 23,459 customers, each once

✓ 23,459 unique customers 3,686 duplicates removed

STEP 04 • CORE PRINCIPLE

The Moral: UNION ALL when every row counts (orders, logs). UNION when one row must mean one person or one payout. Before you pay, check the overlap with INTERSECT.

Story → SQL Connection: Vertical Stacking & Set Operators

Translating the customer merge incident into concrete relational set algebra principles.

Principle 01

Stream Concatenation

Instead of linking rows side-by-side with keys, UNION ALL appends output tuples from separate queries into a single continuous stream.

Principle 02

Pure Mathematical Sets

Set operators treat queries as mathematical sets. UNION combines sets, INTERSECT finds overlap, and EXCEPT finds difference.

Principle 03

Zero Null Padding

Because columns are aligned positionally rather than matched on keys, the output table contains zero artificial NULL columns.

Architectural Truth: Joins answer "What related data belongs to this record?" Set operations answer "What items exist across multiple collections?"

Next: Let us interactively test horizontal versus vertical operations in the visual simulator.

Connection Anchor

Emphasize the mental shift: Joins widen the schema; Set operations lengthen the result set.

Visual Studio: Horizontal Joining vs Vertical Stacking

Click operational modes below to compare horizontal schema expansion with vertical stream concatenation.

EXPANSION DIMENSION:

HORIZONTAL EXPANSION • INCREASES COLUMN WIDTH

FROM orders JOIN customers → [order_id, date] + [name, email] (Width × 2)

Relational Enrichment: A JOIN matches records side-by-side using primary-to-foreign key equality. The output row count depends on cardinality, but column count expands with every joined table.

Pillar 01

Key Equality Matching

Joins require explicit join predicates (ON pk = fk). If no keys match, the relationship produces zero output or requires outer join NULLs.

Pillar 02

Positional Homogeneity

Set operations require zero join keys. Queries are bound purely by physical column order: Column 1 stacks on Column 1, Column 2 on Column 2.

Pillar 03

Schema Width Stability

A UNION query projecting 3 columns will always produce exactly 3 columns in the output, regardless of whether you stack 2 or 20 queries.

Visual Insight: Switch to Mode 2 to see how 5,000 customers and 200 employees stack into 5,200 rows with fixed column width.

Next: Inspect the production query syntax for building unified stakeholder directories.

Visual Demonstration

Toggle between both buttons. Show students how the grid shape changes: JOIN gets wider, UNION gets taller.

UNION vs UNION ALL: Execution Mechanics

How to construct unified entity directories using fast stream concatenation.

Law 01

UNION ALL Speed

UNION ALL appends rows immediately as they stream from each table scan. It requires zero memory buffers and executes in linear $O(N)$ time.

Law 02

Bare UNION Overhead

Bare UNION automatically purges duplicate rows across all projected columns, forcing PostgreSQL to perform expensive sorting or hashing.

Law 03

Constant Origin Tagging

Injecting constant literals like 'Customer' AS contact_role tags each tuple with its originating source entity.

terminal • Master People Directory Query

⇒ Run Query

-- Master People Directory: Fast vertical stacking using UNION ALL
SELECT
  'Customer' AS contact_role,
  first_name || ' ' || last_name AS full_name,
  email
FROM customers.customers
WHERE email IS NOT NULL

UNION ALL

SELECT
  'Store Employee' AS contact_role,
  first_name || ' ' || last_name AS full_name,
  email
FROM stores.employees

UNION ALL

SELECT
  'Vendor Supplier' AS contact_role,
  contact_name AS full_name,
  email
FROM products.suppliers
ORDER BY contact_role, full_name
LIMIT 15;

PostgreSQL 18 Execution: Returns 15 unified directory records across 3 disparate tables with zero null columns.

Next: Visualizing mathematical set algebra with the interactive Venn simulator.

Code Walkthrough

Explain the query: 3 SELECT statements stacked with UNION ALL. Show how 'Customer', 'Store Employee', and 'Vendor Supplier' act as origin tags.

Visual Studio: The Mathematical Set Venn

Click set operators below to observe the mathematical set transformations across two query results.

SET OPERATOR:

UNION ALL • PRESERVES ALL TUPLES • FAST STREAM CONCATENATION

Set A (50 Rows) + Set B (30 Rows) = 80 Total Rows (Duplicates Retained)

Zero Processing Overhead: Appends query streams directly without memory sorting. Best practice when merging disjoint event tables (e.g. Orders and Returns).

Operator 01

UNION ALL (∪ Multiset)

Direct multiset union preserving all occurrences. If a row appears 3 times in A and 2 times in B, it appears 5 times in the output.

Operator 02

UNION (∪ Set)

Mathematical set union. Purges all duplicates, ensuring that every tuple in the compound result set is strictly distinct.

Operator 03

INTERSECT (∩ Overlap)

Isolates rows that exist in BOTH query sets simultaneously. Essential for discovering cross-functional cohort overlap.

Operator 04

EXCEPT (− Difference)

Asymmetric set difference ($A \setminus B$). Returns rows present in the first query but completely absent from the second query.

Algebraic Foundation: PostgreSQL implements all 4 operators with full ANSI SQL standard compliance.

Next: Using EXCEPT to isolate dormant customer churn cohorts on RetailMart.

Venn Demonstration

Cycle through all 4 operators. Point out that INTERSECT and EXCEPT provide a clean mathematical way to solve queries that otherwise require complicated joins.

Cohort Isolation: INTERSECT & EXCEPT Syntax

Using mathematical set difference to identify lapsed accounts and dormant customer cohorts.

Pillar 01

Asymmetric Subtraction

EXCEPT subtracts the second result set from the first. $A \setminus B$ returns rows in $A$ that do not appear in $B$.

Pillar 02

Deduplication by Default

Both INTERSECT and EXCEPT automatically deduplicate results before returning, ensuring single-instance cohort keys.

Pillar 03

Consistent Column Contracts

Both queries project matching column signatures (cust_id, customer_name), allowing EXCEPT to directly isolate churned shoppers.

terminal • Churned Customer Cohort Query

⇒ Run Query

-- Churn Isolation: Customers who ordered in 2024 but NEVER ordered in 2026
SELECT
  o.cust_id,
  c.first_name || ' ' || c.last_name AS customer_name
FROM sales.orders o
JOIN customers.customers c ON o.cust_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
EXCEPT
SELECT
  o.cust_id,
  c.first_name || ' ' || c.last_name AS customer_name
FROM sales.orders o
JOIN customers.customers c ON o.cust_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
ORDER BY cust_id ASC
LIMIT 15;

Production Verification: Isolates 15 historical 2024 customers who have completely lapsed in 2026 for targeted retention outreach.

Next: The 3 strict ANSI schema contracts required for compound queries.

Business Application

Explain that retention teams run EXCEPT queries every week to generate churn lists. It is much cleaner than writing NOT EXISTS or LEFT JOIN ... IS NULL.

Visual Studio: The 3 Strict ANSI Set Contracts

Click contract rules below to see what triggers fatal SQL engine compilation errors in compound queries.

ANSI CONTRACT:

CONTRACT VIOLATION • COLUMN COUNT MISMATCH • FATAL

ERROR: each UNION query must have the same number of columns (Query 1: 3 cols, Query 2: 4 cols)

Strict Arity Requirement: The SQL engine cannot construct a tabular result set if individual SELECT statements project different column counts.

Contract 01

Exact Column Count

Every participating SELECT statement must project the exact same number of columns. Missing columns must be padded with NULL or constants.

Contract 02

Positional Type Matching

Columns align by physical index (1st with 1st, 2nd with 2nd). Position 3 must have mutually compatible types or explicit type casting.

Contract 03

First Query Sets Headers

Output column names and aliases are determined entirely by the FIRST SELECT statement. Column names written in subsequent queries are completely ignored!

Production Tip: Switch to Mode 3 to see the famous alias trap: Query 2 aliases are silently discarded by the SQL compiler.

Next: How to harmonize data types between disparate operational tables using explicit casting.

Contract Rules

Walk through all three contracts. Emphasize Contract 3: many juniors spend hours wondering why their alias in Query 2 doesn't show up in the output.

Positional Schema Binding: Explicit Casting (::)

How to harmonize divergent data types across operational tables to construct valid compound result sets.

Pillar 01

Type Harmonization

Because return IDs use a string prefix ('RET-'), we cast integer order_id::text to satisfy positional type matching at column 2.

Pillar 02

Sign Normalization

Multiplying refunds by -1 (-refund_amount) normalizes debits and credits so financial totals can be summed directly.

Pillar 03

Terminal Global Sort

The ORDER BY transaction_date DESC at the bottom sorts the merged stream chronologically across both orders and returns.

terminal • Harmonized Transactions Query

⇒ Run Query

-- Schema Harmonization: Casting diverse data types to satisfy ANSI set contracts
SELECT
  'Store Order' AS transaction_type,
  order_id::text AS reference_code,
  order_date AS transaction_date,
  net_total::numeric(12,2) AS financial_amount
FROM sales.orders
WHERE order_status = 'Delivered'

UNION ALL

SELECT
  'Product Return' AS transaction_type,
  ('RET-' || return_id)::text AS reference_code,
  return_date AS transaction_date,
  (-refund_amount)::numeric(12,2) AS financial_amount
FROM sales.returns
ORDER BY transaction_date DESC, financial_amount DESC
LIMIT 15;

PostgreSQL 18 Execution: Outputs a single unified chronological ledger combining sales orders and product returns with matching schema headers.

Next: The omnichannel activity feed and global sorting rules in production SQL.

Casting Pattern

Show why order_id::text is required. Without it, PostgreSQL throws: UNION types text and integer cannot be matched.

Visual Studio: The Omnichannel Activity Stream

Click channel feeds below to see how operational silos merge into a single chronological activity ledger.

CHANNEL SOURCE:

SINGLE CHANNEL • SALES ORDERS

[2026-03-15] • Order #8401 • Customer #402 • Net ₹3,450.00

Single-Source Limitation: Viewing sales orders alone hides returns, customer support tickets, and payments, creating an incomplete picture of customer engagement.

Pillar 01

Operational Fragmentation

In large enterprises, orders, payments, and returns live in distinct microservice databases. Set operations reconstruct unified event streams.

Pillar 02

Global Chronological Sort

By sorting on the unified date column at the very end of the compound query, events from all channels interleave in true chronological order.

Pillar 03

Real-Time Auditing

Financial audit and fraud teams rely on unified streams to verify that cash deposits match store payment receipts without data lag.

Production Pattern: Switch to Mode 3 to see how orders and returns merge into an interleaved chronological timeline.

Next: Master the global sorting laws governing multi-query compound statements.

Activity Stream Context

Demonstrate Mode 3. Explain how social media activity feeds (posts + comments + likes) are built using this exact set union pattern.

Set Query Sorting: The Terminal ORDER BY Law

Understanding execution scope, operator precedence, and syntax rules when sorting compound result sets.

Law 01

Terminal ORDER BY Scope

An ORDER BY clause placed at the end of a UNION query applies to the entire compound result set, not just the final SELECT statement.

Law 02

Syntax Error on Inner ORDER BY

Placing ORDER BY directly inside an individual SELECT statement without surrounding parentheses causes an immediate syntax error.

Law 03

Parenthesized Branch Sorting

To sort or limit individual branches before unioning, enclose each SELECT in parentheses: (SELECT ... ORDER BY x LIMIT 5) UNION ALL (...).

terminal • Global Compound Sort Query

⇒ Run Query

-- Multi-Source Transaction Ledger with Terminal Global ORDER BY
SELECT
  'Store Payment' AS transaction_source,
  payment_id AS transaction_ref,
  payment_date AS activity_date,
  payment_mode AS channel_mode,
  amount AS net_amount
FROM sales.payments

UNION ALL

SELECT
  'Corporate Transfer' AS transaction_source,
  transfer_id AS transaction_ref,
  transferred_at::date AS activity_date,
  status AS channel_mode,
  amount AS net_amount
FROM finance.transfer_log
ORDER BY activity_date DESC, net_amount DESC
LIMIT 15;

PostgreSQL 18 Execution: Applies global ORDER BY across both store payments and corporate treasury transfers.

Next: Review the 4 most common traps developers encounter with set operations.

Sorting Rules

Emphasize Law 01 and Law 02. Show that placing ORDER BY in the middle of a query throws a syntax error unless parentheses are used.

Common Anti-Patterns: Set Operations Traps

Avoid these four classical mistakes that degrade performance, cause silent data corruption, or throw parse errors.

Antipattern 01

Defaulting to Bare UNION

Using bare UNION when UNION ALL is sufficient triggers unnecessary memory sorting and temporary disk spills. Unless you specifically require deduplication across datasets, always default to UNION ALL.

Antipattern 02

Silent Positional Column Swapping

If Query 1 selects (first_name, last_name) and Query 2 selects (last_name, first_name), both are TEXT. The database will NOT throw an error! It will silently combine them, corrupting your customer records.

Antipattern 03

Unparenthesized Inner ORDER BY

Placing an ORDER BY clause before a UNION keyword without wrapping the SELECT statement in parentheses causes syntax error at or near UNION. Wrap each branch in parentheses if it requires private sorting.

Antipattern 04

Second Query Column Alias Myth

Expecting column aliases defined in Query 2 or Query 3 to appear in the output. PostgreSQL strictly derives output column headers from the FIRST SELECT statement. Name your columns cleanly in Query 1.

Production Danger: Antipattern 02 is the most insidious bug in data engineering: no crash, no warning, pure silent data corruption!

Next: Enter the Practice Arena Gateway to solve real-world set operations challenges.

Warning Anchor

Warn students about Antipattern 02. If two columns have the same type, PostgreSQL aligns them by position without caring about semantic meaning.

▶ Enter Practice Arena

Practice Arena: Set Operations in Practice

Work through 4 graded challenges below. Test your queries live in the interactive terminal.

Live Arena Execution

Give students 10 minutes to complete the drills. Focus on Challenge 2 (type casting) and Challenge 3 (EXCEPT).

Interview Studio: Set Operations Architecture

Top 8 interview questions asked by Tier-1 product companies and data platform hiring managers.

Performance

“What is the primary architectural difference between UNION and UNION ALL?”

UNION ALL simply concatenates query result streams with zero memory sorting or deduplication overhead ($O(N)$ execution). Bare UNION automatically performs duplicate elimination by sorting or hashing all projected columns ($O(N \log N)$), consuming CPU and temp disk buffers.

Interview Trap: Defaulting to bare UNION when records are known to be distinct or when duplicates are acceptable.

Hiring Manager Tip: When asked Q1, always explain the O(N) stream pass-through vs O(N log N) Sort Unique / Hash Aggregate.

Next: Test your recall with 8 flashcards in the Self-Check studio.

Interview Preparation

Emphasize Q1 and Q7. Interviewers love asking about operator precedence between INTERSECT and UNION.

Self-Check: 8 Core Flashcards

Click each flashcard to test your recall of set contracts, deduplication rules, and precedence laws.

FLASHCARD 01

UNION ALL Speed