UNION, INTERSECT, EXCEPT

Chapter 9 11 code blocks 2269 words

A JOIN adds columns — it makes the result wider. A set operation adds rows — it makes the result taller. That one sentence is the whole chapter; everything else is detail.

UNION vs UNION ALL

Both stack the second result under the first. The difference is duplicates.

-- UNION ALL: keeps everything, including duplicates
SELECT city FROM students
UNION ALL
SELECT city FROM students;
-- 20 rows

-- UNION: removes duplicate ROWS
SELECT city FROM students
UNION
SELECT city FROM students;
-- 7 rows — the 6 distinct cities plus NULL

Result 1

… first 15 of 600 rows

600 rows

Result 2

9 rows

1 ms

A realistic use — one combined directory of everybody in the school:

SELECT first_name || ' ' || last_name AS name, 'student' AS role, city AS extra
FROM students
UNION ALL
SELECT full_name, 'teacher', subject
FROM teachers
ORDER BY role, name;

… first 15 of 312 rows

312 rows

1 ms

Note the added literal column 'student' / 'teacher'. Without it you could not tell the two groups apart afterwards — always tag the source when you stack different things together.

The rules

1. Same number of columns in every branch — otherwise it is an error.
2. Compatible types, position by position. Column 1 of the first query lines up with column 1 of the second regardless of their names.
3. Column names come from the first query. Aliases in later branches are ignored.
4. One ORDER BY, at the very end, applying to the combined result.

-- ERROR: 2 columns vs 3
SELECT first_name, city FROM students
UNION ALL
SELECT full_name, subject, salary FROM teachers;

-- Fixed: pad the shorter branch with a literal so the shapes match
SELECT first_name, city,    NULL   AS salary FROM students
UNION ALL
SELECT full_name,  subject, salary          FROM teachers;

-- ORDER BY belongs to the whole result, so it goes last, once
SELECT first_name AS name FROM students
UNION ALL
SELECT full_name         FROM teachers
ORDER BY name;

INTERSECT and EXCEPT

INTERSECT returns rows present in both results. EXCEPT (called MINUS in Oracle) returns rows in the first that are not in the second. Both remove duplicates.

-- Students taking BOTH Python Basics (1) AND SQL for Analytics (2)
SELECT student_id FROM enrollments WHERE course_id = 1
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 2;
-- 1 (Aziza), 4 (Jasur)

-- Students taking Python Basics but NOT SQL for Analytics
SELECT student_id FROM enrollments WHERE course_id = 1
EXCEPT
SELECT student_id FROM enrollments WHERE course_id = 2;
-- 2 (Bekzod), 7 (Sevara), 10 (Ulugbek)

Result 1

… first 15 of 60 rows

60 rows

Result 2

… first 15 of 74 rows

74 rows

1 ms

Check it by hand — this is exactly why the sample data is small:

SELECT course_id FROM courses
EXCEPT
SELECT course_id FROM enrollments;
-- 6

INTERSECT is also the cleanest way to express "has all of these", which is otherwise awkward:

-- Students enrolled in courses 1 AND 2 AND 5
SELECT student_id FROM enrollments WHERE course_id = 1
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 2
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 5;
-- 1 (Aziza)

… first 15 of 29 rows

29 rows

0 ms

Set operations vs JOIN

The NULL row is the subtle one, and it is the reason EXCEPT succeeds where NOT IN fails.

Real uses

1. Combining data split across tables or periods

Archived data, monthly partitions, two systems being merged — all are UNION ALL:

SELECT * FROM enrollments_2025
UNION ALL
SELECT * FROM enrollments_2026;

2. Adding a totals row to a report

SELECT c.title, COUNT(*) AS enrollments
FROM enrollments e JOIN courses c USING (course_id)
GROUP BY c.course_id, c.title

UNION ALL

SELECT 'TOTAL', COUNT(*) FROM enrollments

ORDER BY (title = 'TOTAL'), enrollments DESC;

TOTAL20

The ORDER BY (title = 'TOTAL') trick sorts FALSE (0) before TRUE (1), pinning the total to the bottom.

3. Unpivot — turning columns into rows

CASE pivots rows into columns (Chapter 8); UNION ALL does the reverse. This shape is exactly what Seaborn wants — long format — and it is the bridge to Part II of this guide:

SELECT title, 'price'   AS metric, price   AS value FROM courses
UNION ALL
SELECT title, 'credits',           credits          FROM courses
ORDER BY title, metric;

… first 15 of 30 rows

30 rows

0 ms

SELECT c.title, e.enrollment_id
FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id

UNION

SELECT c.title, e.enrollment_id
FROM enrollments e LEFT JOIN courses c ON c.course_id = e.course_id;

… first 15 of 2501 rows

2501 rows

3 ms

Here plain UNION is correct, not UNION ALL — the matched rows appear in both halves and must be deduplicated.

Practice set

-- 1. One directory of students and teachers
SELECT first_name || ' ' || last_name AS name, 'student' AS role FROM students
UNION ALL
SELECT full_name, 'teacher' FROM teachers
ORDER BY role, name;

-- 2. Students taking both course 1 and course 2
SELECT student_id FROM enrollments WHERE course_id = 1
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 2;

-- 3. Courses nobody enrolled in
SELECT course_id FROM courses
EXCEPT
SELECT course_id FROM enrollments;

-- 4. All distinct cities and subjects in one column
SELECT city AS value, 'city' AS kind FROM students WHERE city IS NOT NULL
UNION
SELECT subject, 'subject' FROM teachers
ORDER BY kind, value;

-- 5. Course list with a TOTAL row
SELECT c.title, COUNT(*) AS n FROM enrollments e JOIN courses c USING (course_id)
GROUP BY c.course_id, c.title
UNION ALL
SELECT 'TOTAL', COUNT(*) FROM enrollments
ORDER BY (title = 'TOTAL'), n DESC;

-- 6. Unpivot course metrics into long format (Seaborn-ready)
SELECT title, 'price' AS metric, price AS value FROM courses
UNION ALL
SELECT title, 'credits', credits FROM courses;

-- 7. Students who have an active enrollment but never a completed one
SELECT student_id FROM enrollments WHERE status = 'active'
EXCEPT
SELECT student_id FROM enrollments WHERE status = 'completed';

-- 8. Prove UNION vs UNION ALL to yourself
SELECT COUNT(*) FROM (SELECT city FROM students UNION ALL SELECT city FROM students);  -- 20
SELECT COUNT(*) FROM (SELECT city FROM students UNION     SELECT city FROM students);  -- 7

Interview questions

Q: UNION vs UNION ALL?

UNION removes duplicate rows and therefore has to sort or hash the whole result; UNION ALL keeps everything and is much faster. Prefer UNION ALL unless deduplication is actually needed.

Q: UNION vs JOIN?

UNION stacks rows vertically and requires matching column counts and types. JOIN combines columns horizontally and requires a matching condition. Wider vs taller.

Q: What are the requirements for a UNION?

Same number of columns in each branch, compatible types matched by position, and a single ORDER BY at the end. Column names come from the first branch.

Q: What do INTERSECT and EXCEPT do?

INTERSECT returns rows present in both results; EXCEPT returns rows in the first that are absent from the second. Both deduplicate. Oracle calls EXCEPT MINUS; MySQL only got them in 8.0.31.

Q: Why might EXCEPT be safer than NOT IN?

Set operations treat NULLs as equal, so a NULL in the second result does not wipe out the answer the way it does with NOT IN.

Q: How do you add a totals row to a grouped report?

UNION ALL a second query that computes the overall total with a literal label, then sort so the label lands last. (Some databases also offer GROUP BY ROLLUP.)

Q: How would you emulate FULL OUTER JOIN?

A LEFT JOIN B UNION B LEFT JOIN A — with plain UNION, so the rows that matched in both halves are deduplicated.

Summary

• JOIN makes results wider (columns); set operations make them taller (rows).
• UNION ALL keeps duplicates and is fast — make it the default. UNION deduplicates and costs a sort.
• Rules: same column count, compatible types by position not name, names from the first branch, one ORDER BY at the end.
• Pad a shorter branch with NULL AS col to make the shapes match, and tag each branch with a literal so you can tell the sources apart.
• INTERSECT = in both. EXCEPT = in the first only. Both deduplicate and both treat NULLs as equal, which makes EXCEPT a NULL-safe anti-join.
• Real uses: stacking periods or archives, adding a TOTAL row, unpivoting to long format for plotting, and emulating FULL OUTER JOIN.

Next: Chapter 10 — creating and changing data: CREATE, INSERT, UPDATE, DELETE, constraints, transactions and normalization.