INTERSECT

Definition

This topic revisits `INTERSECT` — already introduced conceptually in relational algebra (7.5) — as concrete SQL syntax: returning only the rows present in BOTH of two queries.

Running example — enrolled_2025(student_id) and enrolled_2026(student_id), tracking which students were enrolled each year:

enrolled_2025: 1, 2, 3, 4 enrolled_2026: 2, 3, 5

(Students 2 and 3 stayed enrolled both years; 1 and 4 left after 2025; 5 is a new student in 2026.)

sql
SELECT student_id FROM enrolled_2025
INTERSECT
SELECT student_id FROM enrolled_2026
ORDER BY student_id;

This returns 2 and 3 — the students enrolled in BOTH years. INTERSECT is standard SQL; MySQL added native support in version 8.0.31 (2022) — older MySQL installations lack it and need the WHERE ... IN (subquery) workaround from Chapter 7 instead.

Edge Cases and Pitfalls

• Like UNION, INTERSECT removes duplicates from its result by default (standard set semantics) — a dialect-specific INTERSECT ALL (bag semantics, keeping duplicates present in both sides) exists in some engines but is far less commonly used.
• The same union-compatibility rules (18.5) apply: both queries need matching column counts and compatible types.
• INTERSECT binds with the same general precedence considerations as UNION/EXCEPT when multiple set operations are chained in one statement without parentheses — being explicit with parentheses avoids ambiguity about which operations group together.

Key Takeaways

• INTERSECT returns rows common to both queries — the SQL syntax for relational algebra's Intersection (7.5).
• Native support requires MySQL 8.0.31+; older versions need an IN-subquery workaround.
• INTERSECT deduplicates by default, mirroring UNION's set semantics.