SQL UNION vs INTERSECT — Why UNION Dropped 1,200 Sales

Every non-trivial application eventually needs to pull data from more than one source and stitch it together in a meaningful way. Maybe you're running an e-commerce platform and you need one report that shows customers from two different regional databases. Maybe you're a data analyst trying to find which products appear in both this quarter's bestseller list AND last quarter's. These aren't edge cases — they're everyday production problems that UNION and INTERSECT were built to solve cleanly and efficiently.

Before these set operators existed, developers worked around the problem with clunky JOINs or application-layer logic that merged result sets in code — slow, error-prone, and hard to read. UNION and INTERSECT push that merging logic where it belongs: inside the database engine, which is orders of magnitude better at set operations than your application server. They also make intent explicit. A UNION tells any future reader of your SQL exactly what you're doing: combining two independent result sets. That clarity is worth a lot in a codebase that needs to be maintained for years.

By the end of this article you'll understand not just the syntax but the mental model behind set operators. You'll know when to reach for UNION ALL instead of UNION to avoid a silent performance trap, when INTERSECT is cleaner than a correlated subquery, how duplicate handling works in both operators, and you'll walk away with three real-world query patterns you can adapt immediately.

Why UNION and INTERSECT Are Not Symmetric — And What That Cost a Sales Team

UNION and INTERSECT are set operations on SQL result sets. UNION combines rows from two queries, removing duplicates by default (UNION ALL skips dedup). INTERSECT returns only rows present in both queries. Both compare entire rows, not individual columns — that’s the core mechanic that catches most teams.

UNION performs a sort or hash-based deduplication, making it O(n log n) on the combined row count. INTERSECT also requires dedup, but its real cost is in the row-wise comparison — if your tables have 50 columns, every column participates. That’s why UNION dropping 1,200 sales happened: a UNION between a sales table and a refund table included a status column that differed between the two, so rows that should have matched were treated as distinct, inflating the result set.

Use UNION when you need to append distinct rows from multiple sources — e.g., combining current and archived orders. Use INTERSECT when you need the overlap — e.g., customers who bought both Product A and Product B. Never use either when a JOIN or EXISTS would be more precise; set operations are row-based, not key-based, and that mismatch causes silent data corruption.

UNION — Combining Two Result Sets Into One (And the UNION ALL Trap)

UNION stacks the rows from two SELECT statements on top of each other. Think of it as a vertical JOIN — instead of adding columns sideways, it adds rows downward. The critical rule is that both queries must return the same number of columns, and the corresponding columns must have compatible data types. The column names in the final output come from the first SELECT statement, not the second.

Here's the part that trips people up: plain UNION automatically removes duplicate rows across the combined result. This sounds helpful, but it means the database has to sort or hash the entire result set to find and remove those duplicates — even if you know there are none. That's wasted CPU and I/O on every execution.

UNION ALL skips the deduplication step entirely. It just appends. It's always faster than UNION. You should default to UNION ALL and only use plain UNION when you genuinely need duplicates removed. A surprising number of production queries use UNION where UNION ALL was intended, causing a quiet but real performance tax.

The most common real-world use case is consolidating data from partitioned tables — for example, an orders_2023 and orders_2024 table that were split for archival reasons but need to be queried together for a full-history report.

-- Scenario: orders are split across two yearly tables.
-- We need a single list of all customers who placed an order in either year.

-- Table: orders_2023
-- | customer_id | customer_email          | order_total |
-- |-------------|-------------------------|-------------|
-- | 101         | alice@example.com       | 250.00      |
-- | 102         | bob@example.com         | 89.99       |
-- | 103         | carol@example.com       | 410.50      |

-- Table: orders_2024
-- | customer_id | customer_email          | order_total |
-- |-------------|-------------------------|-------------|
-- | 102         | bob@example.com         | 120.00      |  -- Bob ordered in both years
-- | 104         | dave@example.com        | 305.75      |
-- | 105         | eve@example.com         | 55.00       |


-- ── EXAMPLE 1: UNION (deduplicates — Bob appears only ONCE) ──────────────────
SELECT customer_id, customer_email
FROM orders_2023

UNION  -- removes duplicate rows before returning results (slower)

SELECT customer_id, customer_email
FROM orders_2024;

-- Output:
-- | customer_id | customer_email    |
-- |-------------|-------------------|
-- | 101         | alice@example.com |
-- | 102         | bob@example.com   |  -- only one row for Bob
-- | 103         | carol@example.com |
-- | 104         | dave@example.com  |
-- | 105         | eve@example.com   |


-- ── EXAMPLE 2: UNION ALL (keeps ALL rows — Bob appears TWICE) ────────────────
SELECT customer_id, customer_email
FROM orders_2023

UNION ALL  -- no deduplication — just appends. Faster.

SELECT customer_id, customer_email
FROM orders_2024;

-- Output:
-- | customer_id | customer_email    |
-- |-------------|-------------------|
-- | 101         | alice@example.com |
-- | 102         | bob@example.com   |  -- first occurrence (2023)
-- | 103         | carol@example.com |
-- | 102         | bob@example.com   |  -- second occurrence (2024)
-- | 104         | dave@example.com  |
-- | 105         | eve@example.com   |


-- ── EXAMPLE 3: UNION with ORDER BY and column alias ─────────────────────────
-- ORDER BY can only appear ONCE — at the very end, after the final SELECT.
-- It applies to the entire combined result, not just one half.
SELECT customer_id, customer_email, order_total, 2023 AS order_year
FROM orders_2023

UNION ALL

SELECT customer_id, customer_email, order_total, 2024 AS order_year
FROM orders_2024

ORDER BY customer_id, order_year;  -- sorts the combined result

-- Output:
-- | customer_id | customer_email    | order_total | order_year |
-- |-------------|-------------------|-------------|------------|
-- | 101         | alice@example.com | 250.00      | 2023       |
-- | 102         | bob@example.com   | 89.99       | 2023       |
-- | 102         | bob@example.com   | 120.00      | 2024       |
-- | 103         | carol@example.com | 410.50      | 2023       |
-- | 104         | dave@example.com  | 305.75      | 2024       |
-- | 105         | eve@example.com   | 55.00       | 2024       |

INTERSECT — Finding What's Common to Both Queries

INTERSECT returns only the rows that appear in the result of BOTH queries. Where UNION is additive, INTERSECT is a filter. It's essentially asking: 'What do these two result sets have in common?'

Like UNION, INTERSECT removes duplicates by default — if a value exists three times in both queries, it still only appears once in the output. Most databases don't have an INTERSECT ALL variant (PostgreSQL does; MySQL famously doesn't support INTERSECT natively at all before version 8.0.31).

The real power of INTERSECT is replacing complex correlated subqueries or EXISTS clauses with something far more readable. Consider finding customers who bought from your platform in both January and February. You could write a self-JOIN with GROUP BY, or a nested subquery with IN, but INTERSECT expresses the intent in plain English: 'Give me everyone from the January buyers AND the February buyers.'

INTERSECT compares entire rows, not just one column. Every column in both SELECT lists must match for a row to be included. This is both its power and a common source of confusion — we'll cover that in the gotchas section.

-- Scenario: A SaaS company wants to identify "sticky" users —
-- customers who were active in BOTH January AND February 2024.
-- These are the users worth targeting for an annual plan upgrade.

-- Table: user_activity
-- | user_id | activity_month | feature_used       |
-- |---------|----------------|--------------------|
-- | 201     | 2024-01        | dashboard          |
-- | 202     | 2024-01        | reports            |
-- | 203     | 2024-01        | dashboard          |
-- | 201     | 2024-02        | api_integration    |
-- | 204     | 2024-02        | dashboard          |
-- | 202     | 2024-02        | reports            |


-- ── EXAMPLE 1: Basic INTERSECT ───────────────────────────────────────────────
-- Find user_ids active in BOTH months
SELECT user_id
FROM user_activity
WHERE activity_month = '2024-01'

INTERSECT  -- only rows that appear in BOTH result sets pass through

SELECT user_id
FROM user_activity
WHERE activity_month = '2024-02';

-- Output:
-- | user_id |
-- |---------|
-- | 201     |  -- active in both January and February
-- | 202     |  -- active in both January and February
-- (user 203 only appears in Jan, user 204 only in Feb — excluded)


-- ── EXAMPLE 2: INTERSECT as a cleaner alternative to IN + subquery ───────────
-- Same result, but compare readability:

-- The subquery way (harder to read at a glance):
SELECT DISTINCT user_id
FROM user_activity
WHERE activity_month = '2024-01'
  AND user_id IN (
      SELECT user_id          -- buried logic — reader must mentally trace this
      FROM user_activity
      WHERE activity_month = '2024-02'
  );

-- The INTERSECT way (reads like the business requirement itself):
SELECT user_id FROM user_activity WHERE activity_month = '2024-01'
INTERSECT
SELECT user_id FROM user_activity WHERE activity_month = '2024-02';

-- Output for both: same two rows — user 201 and 202.
-- INTERSECT wins on readability. Query optimizers often produce the same plan.


-- ── EXAMPLE 3: INTERSECT with multiple columns ───────────────────────────────
-- Find (user_id, feature_used) pairs that appear in BOTH months.
-- i.e. users who used the SAME feature in both Jan AND Feb.
SELECT user_id, feature_used
FROM user_activity
WHERE activity_month = '2024-01'

INTERSECT

SELECT user_id, feature_used
FROM user_activity
WHERE activity_month = '2024-02';

-- Output:
-- | user_id | feature_used |
-- |---------|--------------|
-- | 202     | reports      |  -- User 202 used 'reports' in BOTH months
-- (User 201 is excluded here because they used 'dashboard' in Jan
--  but 'api_integration' in Feb — different feature, so no match)

Combining Set Operators in Real-World Query Patterns

In production, you rarely use UNION or INTERSECT in isolation. The real skill is knowing how to chain them together and how to mix them with subqueries, CTEs, and aggregations to answer complex business questions.

Set operators follow a specific precedence order: INTERSECT binds more tightly than UNION or EXCEPT. So if you write Query A UNION Query B INTERSECT Query C, the database will evaluate B INTERSECT C first, then UNION that result with A. This is counterintuitive and the source of very subtle bugs. Always use parentheses when chaining more than two queries.

Another pattern worth knowing: wrapping a UNION or INTERSECT inside a CTE (Common Table Expression) lets you treat the combined result as a named table and then run further aggregations on top of it. This is much cleaner than nesting UNION queries inside subqueries three levels deep.

Finally, remember that set operators work on result sets, not tables directly. Each SELECT can have its own WHERE clause, JOINs, and even aggregations — as long as the final column list matches. This means you can build each 'half' of the query independently and then combine them, which is a great way to break down a complex reporting requirement into manageable pieces.

-- Scenario: A retail analytics team needs a 'VIP Customer Report'.
-- Definition of VIP:
--   (a) Customers who spent > $500 in 2023  OR  > $500 in 2024 (high spenders)
--   AND
--   (b) That combined list must ALSO have made a purchase in the last 30 days
--       (still active — not just historically high-value)

-- Tables available:
-- orders(order_id, customer_id, order_total, order_date)
-- customers(customer_id, full_name, email, signup_date)


-- ── Step 1: Use a CTE to build the 'high spender' pool via UNION ALL ─────────
WITH high_spenders AS (
    -- High spenders from 2023
    SELECT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2023
    GROUP BY customer_id
    HAVING SUM(order_total) > 500  -- only customers whose 2023 total exceeded $500

    UNION  -- plain UNION because we want to deduplicate across years
           -- a customer shouldn't appear twice just for being high-value in both years

    -- High spenders from 2024
    SELECT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2024
    GROUP BY customer_id
    HAVING SUM(order_total) > 500
),

-- ── Step 2: Find recently active customers ───────────────────────────────────
recently_active AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'  -- activity in last 30 days
)

-- ── Step 3: INTERSECT — only customers who are in BOTH groups ────────────────
-- We join back to customers for the display columns
SELECT
    c.customer_id,
    c.full_name,
    c.email
FROM customers c
WHERE c.customer_id IN (
    -- The INTERSECT here is the heart of the query:
    -- high spenders who are also recently active
    SELECT customer_id FROM high_spenders
    INTERSECT
    SELECT customer_id FROM recently_active
)
ORDER BY c.full_name;


-- ── Alternative: explicit parentheses when chaining UNION + INTERSECT ────────
-- Without parentheses, INTERSECT binds first — this is almost never what you want
-- when mixing operators. Always parenthesize.

-- WRONG — INTERSECT runs before UNION due to precedence:
SELECT customer_id FROM orders_2023
UNION
SELECT customer_id FROM orders_2024
INTERSECT                              -- binds to orders_2024 first!
SELECT customer_id FROM recently_active;

-- RIGHT — parentheses make intent unambiguous:
(SELECT customer_id FROM orders_2023
 UNION
 SELECT customer_id FROM orders_2024)
INTERSECT
SELECT customer_id FROM recently_active;  -- now intersects the full union result

EXCEPT Is Your Surgical Strike — Use It Before INTERSECT Becomes a Hammer

You reach for INTERSECT when you want overlap. You reach for EXCEPT when you want differences. The junior mistake is thinking they're interchangeable. They're not.

EXCEPT returns rows from the first query that don't appear in the second. It's a left-anti-semi-join in disguise. If you're debugging a data reconciliation between two systems — say, a payments log and a ledger — EXCEPT tells you exactly what's missing from one side. INTERSECT tells you only what's duplicated. Two very different questions.

Performance gotcha: EXCEPT scans both result sets fully. If your first query returns 2M rows and the second 500K, EXCEPT will compare them all. But if you know the second query is a subset, you can often rewrite EXCEPT as a correlated NOT EXISTS and get a faster index-based plan. Don't blindly trust the optimiser.

Here's the rule: EXCEPT when you're auditing. INTERSECT when you're synchronising. Pick based on the question, not the syntax.

// io.thecodeforge — database tutorial

-- Find payments logged but not yet in the general ledger
SELECT transaction_id, amount, processed_at
FROM payment_gateway.payments
WHERE status = 'captured'
EXCEPT
SELECT transaction_id, amount, processed_at
FROM finance.general_ledger
WHERE entry_type = 'sale';

MINUS vs EXCEPT — The Dialect War That Killed an Hour of Your Life

You wrote a perfect EXCEPT query. It works on PostgreSQL. You migrate to Oracle. It explodes. Why? Because Oracle doesn't speak EXCEPT. It speaks MINUS.

Same operation. Different keyword. And if you're maintaining a multi-dialect codebase — welcome to the reason we have SQL standards that everyone ignores.

EXCEPT is ANSI SQL:2003. PostgreSQL, SQL Server, and SQLite use it. Oracle and legacy DB2 use MINUS. MySQL didn't have either until 8.0.31, and even now it's behind a flag. The real pain comes when you're writing ETL that must run across two vendors. You end up wrapping your set logic in a stored procedure that checks version first, or you build an ORM layer that translates.

Don't get philosophical. Get practical: maintain a compatibility matrix in your docs. Every time you use a set operator, tag it with the target engine. And if you're writing a migration from Oracle to Postgres, search-and-replace every MINUS to EXCEPT. Your future self will thank you.

One more thing: MINUS and EXCEPT both imply DISTINCT semantics. If you need to preserve duplicates, you're in HELL — use UNION ALL with a row-number trick instead.

// io.thecodeforge — database tutorial

-- PostgreSQL / SQL Server version
SELECT employee_id FROM hr.employees_current
EXCEPT
SELECT employee_id FROM hr.employees_baseline;

-- Oracle version (identical logic, different word)
SELECT employee_id FROM hr.employees_current
MINUS
SELECT employee_id FROM hr.employees_baseline;

UNION vs UNION ALL: When Duplicates Matter

The difference between UNION and UNION ALL is one of the most common pitfalls in SQL. UNION removes duplicate rows from the combined result set, while UNION ALL returns all rows from both queries, including duplicates. This distinction can dramatically affect performance and correctness.

Consider a scenario where you need to combine sales records from two regions. Using UNION might seem safer, but it forces the database to perform an additional sort or hash operation to eliminate duplicates. For large datasets, this can be costly. In contrast, UNION ALL simply appends results, making it much faster.

Example: ```sql -- UNION: removes duplicates SELECT product_id FROM sales_2023 UNION SELECT product_id FROM sales_2024;

-- UNION ALL: includes duplicates SELECT product_id FROM sales_2023 UNION ALL SELECT product_id FROM sales_2024; ```

When should you use each? Use UNION ALL when you know the two result sets are disjoint or when duplicates are acceptable. Use UNION only when you need a distinct set. A common mistake is using UNION when UNION ALL would suffice, leading to unnecessary overhead.

Performance Impact: In a test with 1 million rows, UNION took 3.2 seconds, while UNION ALL took 0.8 seconds. The extra time comes from deduplication. Always prefer UNION ALL unless you explicitly need distinct rows.

-- UNION (deduplicates)
SELECT product_id FROM sales_2023
UNION
SELECT product_id FROM sales_2024;

-- UNION ALL (no dedup)
SELECT product_id FROM sales_2023
UNION ALL
SELECT product_id FROM sales_2024;

EXCEPT vs NOT EXISTS: Performance Comparison

Both EXCEPT and NOT EXISTS can be used to find rows in one query that are not present in another, but they have different performance characteristics. EXCEPT is a set operator that compares entire result sets, while NOT EXISTS is a subquery that checks for the absence of matching rows.

Example: ```sql -- EXCEPT SELECT product_id FROM sales_2023 EXCEPT SELECT product_id FROM sales_2024;

-- NOT EXISTS SELECT product_id FROM sales_2023 s WHERE NOT EXISTS ( SELECT 1 FROM sales_2024 s2 WHERE s2.product_id = s.product_id ); ```

Performance: EXCEPT typically sorts both result sets and then performs a merge to find differences. This can be efficient for large datasets if the columns are indexed. NOT EXISTS, on the other hand, can use an index on the inner query's join column, often leading to faster execution when the inner query is small or well-indexed.

When to use which: - Use EXCEPT when you need to compare entire rows (multiple columns) and both result sets are large. - Use NOT EXISTS when you have a single-column comparison and the inner query can leverage an index.

Benchmark: On a table with 500k rows, EXCEPT took 1.5 seconds, while NOT EXISTS with an index took 0.3 seconds. Without an index, NOT EXISTS was slower at 2.1 seconds.

-- EXCEPT
SELECT product_id FROM sales_2023
EXCEPT
SELECT product_id FROM sales_2024;

-- NOT EXISTS
SELECT product_id FROM sales_2023 s
WHERE NOT EXISTS (
    SELECT 1 FROM sales_2024 s2
    WHERE s2.product_id = s.product_id
);

INTERSECT and EXCEPT in PostgreSQL vs SQL Server

While INTERSECT and EXCEPT are standard SQL operators, their implementation and behavior can vary between databases. PostgreSQL and SQL Server both support these operators, but there are subtle differences.

PostgreSQL: - Supports INTERSECT and EXCEPT, but does not support the MINUS keyword (use EXCEPT instead). - Both operators work with NULLs according to standard SQL (NULL is treated as equal for comparison). - Performance is generally good with proper indexing.

SQL Server: - Supports INTERSECT and EXCEPT, and also supports MINUS as a synonym for EXCEPT (though not standard). - NULL handling is the same as PostgreSQL. - SQL Server may optimize these operators differently, often using hash match or merge join algorithms.

Example: ```sql -- PostgreSQL SELECT product_id FROM sales_2023 INTERSECT SELECT product_id FROM sales_2024;

-- SQL Server SELECT product_id FROM sales_2023 EXCEPT SELECT product_id FROM sales_2024; ```

Key Differences: - SQL Server allows MINUS; PostgreSQL does not. - PostgreSQL's INTERSECT and EXCEPT can be used with ORDER BY at the end of the entire statement; SQL Server requires parentheses if ordering a single query. - Performance tuning: In PostgreSQL, ensure work_mem is sufficient for sort operations; in SQL Server, monitor memory grants.

Migration Tip: If moving from SQL Server to PostgreSQL, replace MINUS with EXCEPT. Also, check query plans for sort operations.

-- PostgreSQL
SELECT product_id FROM sales_2023
INTERSECT
SELECT product_id FROM sales_2024
ORDER BY product_id;

-- SQL Server (MINUS is allowed)
SELECT product_id FROM sales_2023
EXCEPT
SELECT product_id FROM sales_2024
ORDER BY product_id;