UNION, INTERSECT, and EXCEPT

Both UNION and UNION ALL combine the result sets of two or more queries by stacking rows vertically. The difference is deduplication. UNION removes duplicate rows — if the same row appears in both queries, it appears only once in the final result. UNION ALL keeps all rows from all queries, including duplicates — if the same row appears in both queries, it appears twice.

The performance implication is significant. UNION must sort or hash all rows to identify and remove duplicates — O(n log n) work. UNION ALL simply appends result sets — O(n) work. UNION ALL is always faster than UNION. On large result sets, this difference can be substantial.

When to use each: use UNION ALL when you know the result sets are disjoint (no possible overlapping rows) — different time periods, different status values, different segments. Use UNION when the same row could appear in multiple queries and you want each unique row once — combining a current customer list with a historical list where some customers appear in both. The common mistake is defaulting to UNION (with deduplication) when UNION ALL is correct and faster. Always ask: "can the same row appear in both queries?" If yes and you want it once, use UNION. If yes and you want it multiple times, or if it is impossible for the same row to appear twice, use UNION ALL.

Three requirements must be satisfied for any set operation (UNION, UNION ALL, INTERSECT, EXCEPT) to execute successfully. First: all queries must return the same number of columns. A SELECT with 3 columns cannot be combined with a SELECT with 4 columns — the column count must match exactly across all queries in the set operation.

Second: the corresponding columns must have compatible data types. Column 1 of query A must be compatible with column 1 of query B — both numeric, both text, both date, etc. Incompatible types (combining a DATE column with an INTEGER column in the same position) cause a type error. Most databases will attempt implicit type casting for closely related types (INTEGER and DECIMAL are usually compatible) but will error on clearly incompatible combinations (DATE and VARCHAR).

Third: ORDER BY and LIMIT must appear after the final query and apply to the entire combined result — not to individual queries within the set operation. Adding ORDER BY to an individual query within UNION causes a syntax error in most databases (PostgreSQL, MySQL 8+). If you need to sort or limit individual queries before combining, wrap each query in a subquery: SELECT * FROM (SELECT ... ORDER BY ... LIMIT n) AS q1 UNION ALL SELECT * FROM (SELECT ... ORDER BY ... LIMIT n) AS q2. The column names in the result come from the first query — if query A has column named "customer_name" and query B has column named "emp_name", the result column is named "customer_name" throughout.

INTERSECT finds rows that appear in both result sets simultaneously — it operates on complete result rows, not on join keys. It is the natural choice when you want to find entities that satisfy two independent conditions that can be expressed as separate queries, and you want the common elements.

INTERSECT is appropriate when: the two queries are logically independent (neither is a "lookup" of the other), both queries naturally return the same columns, and you want the rows that appear in both. Example: customer_ids who placed delivered orders INTERSECT customer_ids who placed cancelled orders — these are two independent queries against the same table, and INTERSECT cleanly finds customers who appear in both.

JOIN is appropriate when you want to combine columns from different tables based on a relationship — horizontally expanding the result. INTERSECT is a vertical set operation — it does not add columns, it finds common rows. A JOIN of customers to orders adds order columns to customer rows. An INTERSECT of delivered customer_ids and cancelled customer_ids finds customer_ids that appear in both sets — no additional columns. When to prefer INTERSECT over a JOIN + filter: when both conditions are expressed on the same table with different WHERE clauses (same column, different values), INTERSECT is more direct. When you need columns from both result sets, EXISTS or JOIN is more appropriate than INTERSECT because INTERSECT does not give you access to the second query's specific rows.

All three find rows in one set that are absent from another, but they work at different levels of abstraction and have different capabilities. EXCEPT operates on complete result sets — it compares entire rows across two queries and returns rows from the first query that are not present in the second. It always deduplicates. It works cleanly when both queries return the same columns naturally.

NOT IN operates on a single column — it filters outer rows where a specific column's value does not appear in a list returned by the subquery. NOT IN fails silently when the subquery contains NULL values. NOT EXISTS is a correlated subquery that checks row-by-row whether a related record exists — it is NULL-safe and can handle complex multi-column or multi-table conditions in the subquery.

Choose based on the problem structure: EXCEPT when both sides are naturally expressed as full queries returning the same columns — "customer_ids who ordered in January" EXCEPT "customer_ids who ordered in February" is a natural set difference. NOT EXISTS when the anti-join condition is complex — involves multiple tables, multiple conditions, or you need flexibility beyond what two parallel queries provide. NOT IN when the subquery is simple, small, and guaranteed NULL-free. EXCEPT is cleanest for comparing two entire queries; NOT EXISTS is most flexible for row-level anti-join with complex conditions. Performance: all three produce anti-join plans in the optimiser for most databases — performance is typically equivalent when proper indexes exist.

Set operations treat NULL values as equal to each other for the purpose of deduplication and comparison — unlike standard SQL WHERE comparisons where NULL ≠ NULL. This is a specific exception to NULL's usual semantics and it is important for understanding UNION, INTERSECT, and EXCEPT behaviour.

In UNION (with deduplication): if both queries return a row where a specific column is NULL, the NULL row appears only once in the result — the database treats the two NULL values as "equal" for deduplication. In INTERSECT: a row with NULL in one query can match a row with NULL in the other query — NULLs are considered equal. In EXCEPT: a row with NULL in the first query is considered matched by a row with NULL in the second query and is therefore excluded from the EXCEPT result.

This NULL-equality behaviour in set operations is defined by the SQL standard and implemented consistently in PostgreSQL, MySQL 8+, and SQL Server. It differs from WHERE NULL = NULL (which evaluates to NULL, not TRUE) and is closer to IS NOT DISTINCT FROM semantics. Practical implication: if you are using EXCEPT to find "new" rows and some rows have NULL keys, those NULL-key rows from the first query will be considered matched by NULL-key rows from the second query and excluded from EXCEPT — which is usually the desired behaviour. When you specifically need NULL to not match NULL in a set operation context, you need a more complex approach using NOT EXISTS with IS NULL checks.

Cause: The two queries being combined with UNION, UNION ALL, INTERSECT, or EXCEPT return different numbers of columns. Set operations require exactly the same column count in all queries. This commonly happens when adding a new column to one query in a set operation without updating the others, or when copying a query template and forgetting to match the column count.

Fix: Count the columns in each SELECT clause and ensure they match. Add NULL AS column_name placeholder columns to queries that have fewer columns: SELECT customer_id, first_name, NULL AS city FROM employees UNION ALL SELECT customer_id, first_name, city FROM customers. Each NULL placeholder occupies a column position and is cast to the appropriate type from the other query's column. Alternatively, remove columns from the query that has more until they match.

Cause: UNION (without ALL) performs deduplication by sorting all combined rows — O(n log n) work even when the result sets are already disjoint and contain no duplicates. The sort is expensive proportional to the total number of rows across all combined queries. With large result sets, this deduplication sort becomes the dominant cost.

Fix: Use UNION ALL instead of UNION if the result sets are disjoint (which they often are when you are combining queries with mutually exclusive WHERE conditions). UNION ALL skips the deduplication sort entirely — O(n) instead of O(n log n). Only use UNION (with deduplication) when the same row could genuinely appear in multiple queries and you need each row once. Verify the result sets are disjoint by confirming their WHERE conditions are mutually exclusive: WHERE status = 'Active' and WHERE status = 'Inactive' cannot produce the same row — use UNION ALL.

Cause: An ORDER BY was placed inside one of the individual queries within a UNION/UNION ALL rather than after the final query. ORDER BY inside a UNION member query is not allowed in standard SQL (PostgreSQL, MySQL 8+) because it would apply only to that query's result before the set operation combines them — which is undefined behaviour since the set operation does not preserve ordering anyway.

Fix: Move ORDER BY to after the last query in the set operation: SELECT ... UNION ALL SELECT ... ORDER BY column. This applies ORDER BY to the entire combined result. If you need to sort and limit individual queries before combining them (for example, top-5 from each source), wrap each query in a subquery: SELECT * FROM (SELECT ... ORDER BY col LIMIT 5) AS q1 UNION ALL SELECT * FROM (SELECT ... ORDER BY col LIMIT 5) AS q2. The subquery wrapping allows ORDER BY and LIMIT to apply to each part independently.

Cause: The two queries passed to EXCEPT return identical result sets, so EXCEPT eliminates all rows from the first query. Or the column types or values differ slightly between the two queries (different precision, different string formatting, leading/trailing whitespace) preventing rows from being considered equal — so EXCEPT does not eliminate any rows from the first query and returns more rows than expected.

Fix: Run each query independently and compare the results visually. Check column types — EXCEPT considers rows identical only when all column values match including type. A DECIMAL(10,2) value of 5.00 may not match an INTEGER value of 5 in an EXCEPT comparison. Ensure consistent data types and formatting across both queries. Use ROUND() or CAST() to normalise numeric precision before EXCEPT. For string columns, use TRIM() and LOWER() to normalise whitespace and case before comparing.

Cause: The rows that should be common between the two queries are not considered identical by INTERSECT because of type or value differences in one or more columns. INTERSECT compares entire rows — all columns must match exactly. If column 3 has a different value or type in the two queries (even by one decimal place, trailing space, or NULL vs empty string), the row is not considered a match.

Fix: Simplify by reducing the columns being compared — only include the columns that define the common identity. SELECT customer_id FROM query_a INTERSECT SELECT customer_id FROM query_b is more reliable than comparing all columns. If you need multi-column INTERSECT, ensure consistent formatting: use the same ROUND() precision, the same TRIM() and LOWER() normalisation, and the same NULL handling (COALESCE to a default value) across both queries. Use FULL OUTER JOIN instead of INTERSECT for debugging — it shows matched and unmatched rows with all column values visible.

Write three queries demonstrating all three set operations: (1) UNION ALL — produce a combined 'activity feed' showing the 5 most recent delivered orders and the 5 most expensive products, with columns: id (order_id or product_id), name (a description), amount (total_amount or unit_price), and type ('Order' or 'Product'). (2) INTERSECT — find store_ids that appear in BOTH delivered orders AND have at least one employee assigned. Show just the store_id. (3) EXCEPT — find product categories that appear in the products table but have NOT appeared in any delivered order's order_items. Show just the category name, sorted alphabetically.