INTERSECT

INTERSECT: rows present in both queries

INTERSECT returns the (distinct) rows that appear in both result sets. The two SELECTs must be union-compatible (same column count and compatible types).

SELECT customer_id FROM Orders2023
INTERSECT
SELECT customer_id FROM Orders2024;     -- customers who ordered in BOTH years

Availability: standard SQL (and PostgreSQL) have always had it; MySQL added INTERSECT in 8.0.31. On older MySQL you emulate it — and the emulation is worth knowing because it shows what INTERSECT really means:

-- Emulation 1: IN (the most readable for a single column)
SELECT DISTINCT customer_id FROM Orders2023
WHERE customer_id IN (SELECT customer_id FROM Orders2024);

-- Emulation 2: EXISTS
SELECT DISTINCT a.customer_id FROM Orders2023 a
WHERE EXISTS (SELECT 1 FROM Orders2024 b WHERE b.customer_id = a.customer_id);

-- Emulation 3: INNER JOIN
SELECT DISTINCT a.customer_id FROM Orders2023 a
JOIN Orders2024 b ON a.customer_id = b.customer_id;

Worked multiset trace

Bag tables (bag / multiset of customer ids as they appear in each year):

Orders2023.customer_id	Orders2024.customer_id
A	A
A	B
B	B
C	D
NULL	NULL

Row-by-row membership for distinct INTERSECT:

Value	In 2023?	In 2024?	In INTERSECT?
A	yes (×2)	yes (×1)	yes (once) — plain INTERSECT de-duplicates
B	yes	yes (×2)	yes (once)
C	yes	no	no
D	no	yes	no
NULL	yes	yes	yes (once) — under set ops, NULL matches NULL

Result of plain INTERSECT: {A, B, NULL} (order not guaranteed).

INTERSECT ALL (multiplicity)

Postgres (and the standard) support INTERSECT ALL: multiplicity is the minimum of the two bags.

Value	Count 2023	Count 2024	INTERSECT ALL count
A	2	1	min(2,1) = 1
B	1	2	min(1,2) = 1
NULL	1	1	1

Emulating ALL with JOIN without care multiplies rows (2×2 = 4 for a key) — use carefully or stick to DISTINCT INTERSECT unless you need bags.

NULL-as-equal under set operations

In WHERE, NULL = NULL is UNKNOWN. In UNION/INTERSECT/EXCEPT, two rows that are equal on every column with NULLs in the same positions are treated as duplicates — NULL counts as equal to NULL for set membership. That is why NULL appears in the INTERSECT result above. Do not assume set ops use the same equality as =.

Pitfalls

• INTERSECT returns distinct rows; if you need to keep duplicate multiplicity use INTERSECT ALL (Postgres) — plain JOIN can multiply rows, so add DISTINCT when emulating.
• For multi-column intersection, the EXISTS/JOIN forms must match on every column.
• Column order and types must match left-to-right; names in the second SELECT are ignored for the result header (first SELECT wins).

Takeaways

• INTERSECT = rows in both queries, de-duplicated; native in PostgreSQL and MySQL ≥ 8.0.31.
• INTERSECT ALL uses min multiplicity; plain INTERSECT is a set.
• Set ops treat NULL as equal to NULL; WHERE does not.
• Emulate with IN / EXISTS / JOIN + DISTINCT on older MySQL.

---

Re-authored for correctness and worked multiset/NULL traces for this guide. Per the ISO SQL standard & MySQL 8.0.31 release notes. See also: UNION, EXCEPT, Subqueries.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — INTERSECT

Why this concept exists (judgment layer)

INTERSECT is set membership in both queries — customers in both years, users in both segments. Multiset INTERSECT ALL and NULL-as-equal under set ops are the advanced traps.

Mental model (install this intuition)

Plain INTERSECT = distinct rows in both. INTERSECT ALL multiplicity = min(count_L, count_R). NULL matches NULL under set ops. Emulate with EXISTS/IN/JOIN + DISTINCT on old MySQL.

Worked example with numbers or traced steps

2023: A,A,B,C,NULL  2024: A,B,B,D,NULL
INTERSECT: {A,B,NULL} each once
INTERSECT ALL: A×1, B×1, NULL×1 (min counts)
JOIN without DISTINCT can explode (A×2 × A×1 if not careful on multi-col)
MySQL native INTERSECT since 8.0.31

When NOT to use / named alternative

Prefer INNER JOIN when you need columns from both sides in one row — INTERSECT returns one side's projection only. Prefer EXISTS for correlated semi-join with extra filters.

Failure mode & ops fingerprint

Fingerprint: JOIN emulation multiplies rows; forgetting DISTINCT; assuming NULL excluded from INTERSECT (it is included when both sides have NULL); type mismatch on column order.

Hostile-panel drills (defend the decision)

Q1. INTERSECT ALL for A counts 2 and 5?
Model answer: min(2,5)=2 copies of A in the result bag.

Q2. Why NOT IN is a bad INTERSECT substitute for multi-set anti-patterns?
Model answer: NOT IN is for difference/anti-join and breaks with NULL; INTERSECT is dual to membership in both — use EXISTS for semi-join.

Q3. Result header column names?
Model answer: First SELECT's names win; second SELECT must be type/position compatible.

I just read a lesson on **INTERSECT** (Databases) and want to truly understand it. Explain INTERSECT from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **INTERSECT** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **INTERSECT** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **INTERSECT** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.