Intermediate8 min read PostgreSQL

Set Operations — UNION, INTERSECT, EXCEPT

Combine or compare result sets using UNION, INTERSECT, and EXCEPT — the SQL equivalent of Venn diagram operations.

UNION
INTERSECT
EXCEPT
UNION ALL
set operations
retention

Use this when you need to:

UNION — combine results from two tables (current + archive, multiple regions, A/B test arms)
INTERSECT — customers who bought BOTH product A and product B, students enrolled in BOTH semesters
EXCEPT — find what's in one set but not the other (missing follow-ups, churn signals, dropped users)
Reconcile two snapshots — what's new since yesterday? what dropped out?

Set operations combine the results of two SELECT queries into one result set. Think of them as Venn diagram operations: UNION is the full combined area, INTERSECT is the overlap, and EXCEPT is one side minus the other.

Interactive — Set Operations

UNION returns every distinct row from either query — the full Venn diagram, duplicates removed. Use UNION ALL to keep duplicates.

-- Every student enrolled in either term (no duplicates)
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2024FA'
UNION
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2027FA';

Both queries must return the same number of columns, and corresponding columns must have compatible types. Column names in the final result come from the first query.

UNION — Combine, Remove Duplicates

-- All student IDs with a term record in either term (duplicates removed)
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2026FA'
UNION
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2027SP';

-- UNION ALL keeps duplicates — use when you want to count all occurrences
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2026FA'
UNION ALL
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2027SP';

INTERSECT — Only Rows in Both

-- Students registered in BOTH Fall 2026 AND Spring 2027 (retained): 81 rows
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2026FA'
  AND sttr_current_status = 'R'
INTERSECT
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2027SP'
  AND sttr_current_status = 'R';

EXCEPT — Rows in First, Not Second

-- Students registered in Fall 2026 who were NOT registered in Spring 2027: 37 rows
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2026FA'
  AND sttr_current_status = 'R'
EXCEPT
SELECT sttr_student FROM ods_student_terms WHERE sttr_term = '2027SP'
  AND sttr_current_status = 'R';

Real-World: Term-Over-Term Retention Report

-- Label students as Retained or Lost, then combine into one report
SELECT sttr_student, 'Retained' AS cohort
FROM ods_student_terms WHERE sttr_term = '2026FA' AND sttr_current_status = 'R'
INTERSECT
SELECT sttr_student, 'Retained'
FROM ods_student_terms WHERE sttr_term = '2027SP' AND sttr_current_status = 'R'

UNION ALL

(
    SELECT sttr_student, 'Lost' AS cohort
    FROM ods_student_terms WHERE sttr_term = '2026FA' AND sttr_current_status = 'R'
    EXCEPT
    SELECT sttr_student, 'Lost'
    FROM ods_student_terms WHERE sttr_term = '2027SP' AND sttr_current_status = 'R'
)
ORDER BY cohort, sttr_student;

Practice challenge

Find the courses (sec_course) that had a section in the 2024FA term but none in 2025FA. Use the ods_course_sections table.

Solution

-- EXCEPT already removes duplicates, so no DISTINCT is needed
SELECT sec_course
FROM ods_course_sections
WHERE sec_term = '2024FA'
EXCEPT
SELECT sec_course
FROM ods_course_sections
WHERE sec_term = '2025FA'
ORDER BY sec_course;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons