Intersection

Definition

Intersection (symbol: ∩) returns only the rows that appear in BOTH of two union-compatible relations — the overlap between them. Like Union, it requires the two relations to have the same number of columns with compatible types.

How It Works

sql
SELECT student_id FROM honors_students
INTERSECT
SELECT student_id FROM deans_list
ORDER BY student_id;

This returns only the student_id values present in BOTH the honors list AND the dean's list — students who achieved both distinctions. INTERSECT is directly supported in standard SQL and PostgreSQL/SQL Server; some dialects (notably older MySQL versions) lacked it and required simulating it with an INNER JOIN or a WHERE ... IN (subquery) pattern instead — INTERSECT support has become more standard in modern MySQL as well, but checking dialect support before relying on it is still worthwhile.

Edge Cases and Pitfalls

• Where native INTERSECT isn't available, the standard workaround is: SELECT student_id FROM honors_students WHERE student_id IN (SELECT student_id FROM deans_list) — logically equivalent, just phrased differently.
• Like UNION, INTERSECT in standard SQL removes duplicates from its result by default; some dialects offer an INTERSECT ALL variant with bag semantics, mirroring the UNION/UNION ALL distinction.
• Intersection is NOT the same as an inner join: intersection compares WHOLE ROWS for equality across two union-compatible (same-shape) relations, while a join combines rows from two relations of potentially DIFFERENT shapes based on a matching condition on specific columns.

Key Takeaways

• Intersection (∩) returns rows common to both of two union-compatible relations.
• SQL's INTERSECT implements it directly (where supported); an IN/INNER JOIN pattern simulates it otherwise.
• Intersection compares whole rows across same-shaped relations — it is conceptually different from a join.