Set operations stack rows and compare them whole

The idea

A JOIN adds columns: it takes a row from one table and attaches the matching row from another. A set operation adds rows. It runs two queries and combines their results as sets of rows, one result stacked under the other.

There are four. UNION ALL stacks and keeps everything. UNION stacks and removes repeated rows. INTERSECT keeps only the rows that are in both results. EXCEPT keeps rows from the first result that are not in the second.

Why it exists

Real data about one subject lives in several places with the same shape. Orders are in orders, returns in returns, sessions in web_sessions: three different tables, but each can be reduced to "a date, a kind of event, an amount". Stacking them gives one activity table, which no join can build, because there is no key connecting a return to a session.

Set operations also answer questions about overlap, which joins make clumsy. "Who bought in both years?" "Who bought last year and not this year?" These are statements about lists of customers, and a set operation says it as directly as the question does.

How it works under the hood

Rows are compared whole, and columns match by position

The database takes the first result's columns and lines up the second result's columns under them, by position, not by name. Column 1 goes with column 1. The rules follow directly:

• both queries must return the same number of columns;
• the types in each position must be compatible (a number under a date is an error, and a number under text is a silent conversion, so check it);
• the column names come from the first query;
• ORDER BY goes once at the very end and applies to the whole combined result.

If the second query selects (order_id, customer_id) and the first selects (customer_id, order_id), nothing complains and the result is nonsense: ids sit in the wrong columns. Put a label column in each branch ('order' AS kind, 'return' AS kind) so a stacked table says where each row came from.

UNION removes duplicates, and that costs you

UNION is UNION ALL followed by a distinct step. Two things follow. It is slower on big results, because the database has to sort or hash every row. And it can delete rows you meant to keep.

Two legitimate refunds with the same date and amount are identical as selected rows, so UNION merges them and the total falls. If you are adding up an amount, UNION ALL is the safe default, and you reach for UNION only when you actually want a list without repeats.

INTERSECT and EXCEPT

INTERSECT and EXCEPT also compare whole rows, and both remove duplicates. Use them for questions about people or products.

These are the building blocks of a customer-status report. Retained customers are in this year's buyers and last year's. New customers are in this year's and never earlier. Lapsed customers are in last year's and not this year's. Reactivated customers bought this year, not last year, but did buy before that. Each group is one set operation, and a final UNION ALL with a label puts them in one table with the amount each group spent.

NULL compares as equal here

In a WHERE, NULL = NULL is unknown. In a set operation, two NULLs in the same position count as the same value, so duplicates containing NULLs are removed as expected. It is the one place in SQL where NULLs behave like normal values.

Types and labels in practice

When the two branches come from different tables, give each one the same shape by hand. Cast a date to DATE in both, write 0 or CAST(NULL AS DECIMAL(10,2)) where a branch has no amount, and keep the column order identical. A little tedium here saves an afternoon later.

Alternatives

INTERSECT can be written as an INNER JOIN or WHERE … IN, and EXCEPT as WHERE NOT EXISTS or a LEFT JOIN … IS NULL. They give the same answer when the key is unique. The set operation is usually shorter and says what you mean, but a join lets you bring in extra columns such as spend.

Checking a stacked result

Set operations have tidy arithmetic you can use as a test. UNION ALL returns exactly the first result's row count plus the second's: 3 + 1 = 4 in the diagram. UNION returns at most that many, and the gap tells you how many duplicates it removed. For the customer groups, retained plus lapsed must equal last year's buyers, because every one of them either came back or did not. If the numbers do not add up, one of the branches uses a different definition of "bought", for example one excludes cancelled orders and the other does not. Fix the definition in a shared CTE so both branches read from the same list.

Common misconceptions

"UNION is the normal one, UNION ALL is the special one"

The other way round. UNION ALL does exactly what stacking suggests and is faster. UNION adds a dedupe you must have a reason for. When you total a number, default to UNION ALL.

"The columns are matched by name"

By position. Nothing checks that your second query's columns mean the same thing, only that there are the same number of them with compatible types. Read both SELECT lists side by side before you trust a stacked result.

"EXCEPT is symmetric, like INTERSECT"

A EXCEPT B and B EXCEPT A are different questions: lapsed customers versus new ones. Always decide which result is the first, and say so in a comment.