What are SET operations

A set operation takes the output rows of two SELECT queries and combines them by treating each row as an element of a set, matching rows by comparing every selected column position-by-position (not by column name) and resolving duplicates according to the operator: UNION and friends are vertical row-stacking operations, in contrast to JOINs, which stitch tables horizontally column-by-column.

There are four logical set operators in standard SQL, organized as two pairs. Each pair has a default form that removes duplicates and an ALL form that keeps them:

Worked example: two tables of city visitors

Suppose two analysts each pulled a list of customer cities. We want to reason about overlap. Take these two row-sets:

Notice A itself contains Delhi twice and B contains Pune twice — duplicates exist inside each input, which is exactly where the ALL distinction bites. Tracing each operator on these inputs:

1. A UNION ALL B → just concatenates all 6 rows: {Pune, Delhi, Delhi, Pune, Pune, Surat}. No dedupe, no sort guaranteed.
2. A UNION B → take the same 6 rows, then collapse to distinct values: {Pune, Delhi, Surat} (3 rows).
3. A INTERSECT B → values present in both, deduped: Pune is in both; Delhi is only in A; Surat only in B → {Pune} (1 row).
4. A EXCEPT B → distinct values of A with anything appearing in B removed: A has {Pune, Delhi}; drop Pune (it's in B) → {Delhi} (1 row).

The portability bug this page used to contain

The older version of this page listed all four operators as the "primary set operations in MySQL." That is wrong and would burn you in practice: MySQL had no native INTERSECT or EXCEPT until version 8.0.31 (released late 2022). On MySQL 5.7 and 8.0.0–8.0.30 — still common in production — only UNION and UNION ALL exist. Writing SELECT ... INTERSECT SELECT ... there is a syntax error.

Why the naive listing is wrong: it implies you can reach for any of the four on MySQL, but the next lessons themselves emulate INTERSECT/EXCEPT with joins and NOT IN precisely because the engine lacked them. The honest statement is: UNION/UNION ALL are universal; INTERSECT/EXCEPT are standard SQL, present in PostgreSQL, SQL Server, Oracle (as MINUS) and SQLite for years, but only in MySQL 8.0.31+.

Emulating INTERSECT / EXCEPT on old MySQL

-- INTERSECT (distinct rows in both)
SELECT DISTINCT a.city FROM A a
WHERE a.city IN (SELECT city FROM B);

-- EXCEPT (distinct rows in A but not B)
SELECT DISTINCT a.city FROM A a
WHERE a.city NOT IN (SELECT city FROM B WHERE city IS NOT NULL);

The WHERE city IS NOT NULL guard in the EXCEPT emulation is load-bearing: a single NULL inside a NOT IN subquery makes the whole predicate evaluate to UNKNOWN for every row, so the query silently returns zero rows. The real EXCEPT operator does not have this trap because it treats NULLs as equal to each other for matching.

Pitfalls

• Column count / type mismatch. Both SELECTs must return the same number of columns in compatible types, or you get an error like "each UNION query must have the same number of columns." The result's column names come from the first SELECT only.
• Positional, not by name. The engine matches column 1 to column 1. If you reorder columns in the second SELECT, it unions mismatched data with no warning (see the amber box above). This is a classic silent data-corruption bug.
• UNION quietly dedupes — and that costs. Plain UNION must sort or hash all rows to remove duplicates. If you know the inputs are already disjoint (or you want every row), use UNION ALL: it skips the dedupe pass and is dramatically cheaper on large result sets.
• ORDER BY applies to the whole result. You can only put one ORDER BY at the very end of the combined query, not on each branch (some engines reject a per-branch ORDER BY outright). LIMIT on a branch needs parentheses around that SELECT.
• NULLs match each other. Inside a set operation, two NULL rows are considered equal and collapse under UNION/INTERSECT — the opposite of the usual NULL = NULL → UNKNOWN rule everywhere else in SQL.

Takeaways

• Set operators stack rows vertically and match by column position; JOINs stitch columns horizontally and match by predicate. Reach for set operators when both sides have the same shape.
• Two pairs: UNION/INTERSECT/EXCEPT remove duplicates; the ALL form keeps them. UNION ALL is the cheap default when you don't need deduplication.
• Only UNION and UNION ALL are universal. INTERSECT/EXCEPT are standard but only landed in MySQL 8.0.31 — on older MySQL, emulate them with IN / NOT IN (and guard NOT IN against NULLs).
• Inside set operations, NULL equals NULL — a deliberate exception to SQL's usual three-valued logic.

Sources: ISO/IEC 9075 SQL standard (set operators, duplicate semantics); PostgreSQL documentation, "Combining Queries (UNION, INTERSECT, EXCEPT)"; MySQL 8.0 Reference Manual, "Set Operations with UNION, INTERSECT, and EXCEPT" (INTERSECT/EXCEPT added in 8.0.31); Oracle Database SQL Language Reference (MINUS); Joe Celko, "SQL for Smarties," on NOT IN / NULL behavior. Re-authored and deepened for this guide: fixed the incorrect claim that MySQL natively supports INTERSECT/EXCEPT, added the mechanism, a positional-matching trace, and the NULL-in-NOT-IN pitfall.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — What are SET operations

Why this concept exists (judgment chain)

Set ops stack rows vertically and match by ordinal position/types, not names. UNION dedupes (sort/hash cost); UNION ALL concatenates. INTERSECT/EXCEPT are standard but only native in MySQL since 8.0.31 — portability is a production fact, not trivia. NULL equals NULL inside set ops.

Worked example with numbers or traced steps

A={Pune,Delhi,Delhi}; B={Pune,Pune,Surat}
UNION ALL → 6 rows; UNION → {Pune,Delhi,Surat}
INTERSECT → {Pune}; EXCEPT → {Delhi}
Positional bug: SELECT city,country UNION SELECT country,city silently mismatches.
MySQL <8.0.31: INTERSECT/EXCEPT syntax error — emulate with IN / NOT IN + IS NOT NULL guard.
Set-op NULL matching collapses two all-NULL rows under UNION.

When NOT to use / named alternative

Prefer JOIN when you need horizontal attributes from both sides. Prefer UNION ALL when inputs are disjoint or duplicates are wanted — skip dedupe cost. Emulate EXCEPT with NOT EXISTS rather than NOT IN when nullable. Avoid set ops for wide rows when you only need key membership.

Failure / ops fingerprint

Cross-engine deploy fails on INTERSECT. Silent garbage from column order swap. UNION spiking CPU/temp on huge bags. NOT IN emulation returns empty after a NULL sneaks into B. Ops: CI matrix tests MySQL version gates; EXPLAIN for HashAggregate/Unique on UNION.

Hostile-panel Q&As (model answers)

Q1. UNION vs UNION ALL performance?
Model answer: UNION must dedupe (sort/hash); UNION ALL is pure append — default when safe.

Q2. MySQL INTERSECT availability?
Model answer: Native only from 8.0.31; older versions need IN/EXISTS rewrites.

Q3. Why guard NOT IN with IS NOT NULL?
Model answer: A single NULL in the subquery makes x NOT IN (...) UNKNOWN for all x → empty result.