UNION, INTERSECT, EXCEPT, Query Composition, and Readability Patterns

Learning outcomes

Set operations combine complete query results rather than matching rows by a join predicate. They are ideal when the business question is naturally “rows in either set,” “rows common to both sets,” or “rows in the first set but not the second.” MySQL 8.4 supports UNION, INTERSECT, and EXCEPT, including ALL and DISTINCT forms. Correct use depends on column compatibility, duplicate semantics, operator precedence, and where ordering/limits are applied.

UNION versus UNION ALL: decide what duplicates mean

UNION without ALL uses duplicate-eliminating semantics. UNION ALL keeps every row from every branch. De-duplication is not merely a performance switch; it changes the result. Start from the business meaning.

USE servicehub_query_lab;-- Open orders with priority 1 or 2.SELECT work_order_idFROM work_ordersWHERE status='open' AND priority IN (1,2)UNION-- Orders tagged urgent.SELECT work_order_idFROM work_order_tagsWHERE tag='urgent'ORDER BY work_order_id;-- Compare duplicate-preserving semantics.SELECT work_order_idFROM work_ordersWHERE status='open' AND priority IN (1,2)UNION ALLSELECT work_order_idFROM work_order_tagsWHERE tag='urgent'ORDER BY work_order_id;

Work orders 1001 and 1004 occur in both branches. With UNION, each appears once; with UNION ALL, each appears twice. If duplicate occurrence represents two independent facts your caller needs, ALL may be correct. If the result represents a set of unique work-order IDs, duplicate elimination matches the model.

INTERSECT: rows common to both result sets

INTERSECT returns rows present in both sides. This directly expresses “open orders that are also urgent.”

SELECT work_order_idFROM work_ordersWHERE status='open'INTERSECTSELECT work_order_idFROM work_order_tagsWHERE tag='urgent'ORDER BY work_order_id;-- Expected: 1001, 1004

By default, duplicate elimination applies. INTERSECT ALL has multiset semantics and can retain repeated rows according to how many occurrences exist on each side. Use it only when duplicate counts are meaningful and tested.

EXCEPT: rows in the first result but not the second

EXCEPT is directional: A EXCEPT B is not the same as B EXCEPT A. This makes it suitable for questions such as “open work orders that are not urgent.”

SELECT work_order_idFROM work_ordersWHERE status='open'EXCEPTSELECT work_order_idFROM work_order_tagsWHERE tag='urgent'ORDER BY work_order_id;-- Expected: 1003, 1006, 1007

This query avoids the NULL trap from NOT IN because it operates on result rows under set-operation semantics. That does not mean EXCEPT should mechanically replace every anti-join or NOT EXISTS; choose the formulation that best represents the full row/value logic and performs acceptably under evidence.

Column-count and type compatibility are part of the contract

Each operand must produce the same number of columns. The corresponding result columns must also be type-compatible under MySQL’s set-operation rules. The output column names are generally determined by the first query block, so aliases in later branches do not rename the final result.

-- Wrong: first branch returns two columns, second returns one.SELECT work_order_id, statusFROM work_ordersUNIONSELECT customer_idFROM customers;-- Expected: an error that the SELECT statements have a different number of columns.-- Repair: make both branches describe the same shaped concept.SELECT CAST(work_order_id AS CHAR) AS entity_id,       'work_order' AS entity_typeFROM work_ordersUNION ALLSELECT CAST(customer_id AS CHAR),       'customer'FROM customersORDER BY entity_type, entity_id;

The repair is not “pad with random NULLs until syntax passes.” Define what each output column means and make every branch conform to that same relation.

Precedence: INTERSECT binds before UNION and EXCEPT

MySQL evaluates INTERSECT before UNION or EXCEPT. If a mixed expression’s intended grouping is not obvious, use parentheses even when the default precedence already matches your intent. Future maintainers should not need to memorize precedence to verify correctness.

-- MySQL groups the INTERSECT first.SELECT work_order_id FROM work_orders WHERE customer_id IN (1,3)EXCEPTSELECT work_order_id FROM work_orders WHERE status='closed'INTERSECTSELECT work_order_id FROM work_order_tags WHERE tag='urgent';-- Equivalent grouping made explicit.SELECT work_order_id FROM work_orders WHERE customer_id IN (1,3)EXCEPT(  SELECT work_order_id FROM work_orders WHERE status='closed'  INTERSECT  SELECT work_order_id FROM work_order_tags WHERE tag='urgent');

ORDER BY and LIMIT apply at the level where you place them

A final ORDER BY sorts the combined result. If a branch itself must be ordered or limited before the set operation, wrap that query expression in parentheses. Branch-level ordering without a limiting/semantic purpose is often irrelevant because set operations do not promise to preserve source ordering.

-- Take the two earliest open orders and two latest closed orders,-- then combine those chosen rows.(  SELECT work_order_id, opened_at, 'open' AS source_set  FROM work_orders  WHERE status='open'  ORDER BY opened_at, work_order_id  LIMIT 2)UNION ALL(  SELECT work_order_id, opened_at, 'closed' AS source_set  FROM work_orders  WHERE status='closed'  ORDER BY opened_at DESC, work_order_id DESC  LIMIT 2)ORDER BY source_set, work_order_id;-- Different meaning: combine first, then take only two final rows.SELECT work_order_id, opened_atFROM work_orders WHERE status='open'UNION ALLSELECT work_order_id, opened_atFROM work_orders WHERE status='closed'ORDER BY opened_at, work_order_idLIMIT 2;

These are different questions. Parentheses are not decoration; they define which query expression receives a branch-level ORDER BY/LIMIT.

Failure drill: using UNION as a substitute for a data model

Another common mistake is to union unrelated entities simply because their columns can be coerced into the same shape. The query may execute but produce a weak contract that downstream code cannot interpret safely. Set operations are strongest when each branch represents the same conceptual row type.

Hands-on lab: set algebra for ServiceHub

1. Define set A as all open work-order IDs and set B as all urgent-tag work-order IDs.
2. Run A UNION B, A UNION ALL B, A INTERSECT B, and A EXCEPT B. Explain duplicate and directional behavior.
3. Reverse the EXCEPT operands and explain why the result changes.
4. Trigger the mismatched-column-count error and repair it by defining a coherent two-column entity feed.
5. Run a mixed EXCEPT/INTERSECT statement both with implicit precedence and explicit parentheses; verify the results match.
6. Compare branch-level LIMIT with final-result LIMIT and explain why the row sets differ.

-- A ∩ B: open and urgentSELECT work_order_id FROM work_orders WHERE status='open'INTERSECTSELECT work_order_id FROM work_order_tags WHERE tag='urgent'ORDER BY work_order_id;-- 1001, 1004-- A - B: open but not urgentSELECT work_order_id FROM work_orders WHERE status='open'EXCEPTSELECT work_order_id FROM work_order_tags WHERE tag='urgent'ORDER BY work_order_id;-- 1003, 1006, 1007

Chapter synthesis and production judgment

Chapter 04 built a single query-reliability story. SELECT semantics depend on explicit predicates, NULL handling, and ordering. Joins require cardinality reasoning. Subqueries impose scalar, existence, membership, and derived-table contracts. Recursive CTEs add termination and cycle safety. Set operations combine whole relations with explicit duplicate, compatibility, precedence, and ordering rules.

In production, keep representative correctness fixtures for complex queries, not only performance benchmarks. Capture plan evidence when queries become slow, but preserve the intended result as the primary contract. Watch for schema changes that alter NULLability or key uniqueness, because they can invalidate assumptions made by joins and subqueries even when SQL still parses.

Next chapter: move from relational composition into advanced analytical SQL—aggregation, functional dependence, window frames, JSON_TABLE, date/string/regular-expression functions, and reporting patterns.

Authoritative references

• MySQL 8.4 Reference Manual — Set Operations with UNION, INTERSECT, and EXCEPT
• MySQL 8.4 Reference Manual — UNION Clause
• MySQL 8.4 Reference Manual — INTERSECT Clause
• MySQL 8.4 Reference Manual — EXCEPT Clause
• MySQL 8.4 Reference Manual — Parenthesized Query Expressions
• MySQL 8.4 Reference Manual — SELECT Statement