Set Operators

A join widens a result by adding columns from another table. A set operator does the opposite. It takes two complete query results and combines them vertically, as sets of rows.

Think of the two queries as two lists on a desk, and the operator as the rule for merging them.

The four operators#

Oracle spells EXCEPT as MINUS. MySQL gained INTERSECT and EXCEPT only in version 8.0.31, so older installs lack them. PostgreSQL and SQL Server have always had them.

UNION ALL: stacking lists#

The simplest use is combining similar data from different places. Here, one list of every name in the system:

SELECT full_name, 'customer' AS kind FROM customers
UNION ALL
SELECT full_name, 'employee' FROM employees
ORDER BY full_name;

That returns 20 rows, 10 from each table. The kind literal tells you where each row came from, which is a habit worth keeping whenever you stack sources.

UNION versus UNION ALL#

UNION removes duplicate rows from the combined result. UNION ALL does not.

Take customers who ordered before April, and customers with an open pending order:

SELECT customer_id FROM orders WHERE order_date < '2024-04-01'
UNION
SELECT customer_id FROM orders WHERE status = 'pending'
ORDER BY customer_id;

 customer_id
-------------
           1
           2
           3
           4
           5
           6
           9
(7 rows)

Swap in UNION ALL and you get 8 rows, because customer 1 placed two pre-April orders and each one appears. The first query returns six rows before the pending orders are added (orders 1 to 6), and UNION collapses customer 1's two rows into one.

Here is the cost difference. UNION ALL just appends one result to the other. UNION has to compare every row against every other, which means a sort or a hash table over the whole combined set. On large results that is real time and memory.

Default to UNION ALL. Use UNION only when duplicates are possible and unwanted, and say so in a comment.

INTERSECT and EXCEPT#

Split customers by when they ordered:

-- Customers who ordered before April AND from April onward (repeat buyers across periods)
SELECT customer_id FROM orders WHERE order_date <  '2024-04-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2024-04-01'
ORDER BY customer_id;
-- 1, 2, 3

-- Customers who ordered before April but never since (lapsed)
SELECT customer_id FROM orders WHERE order_date <  '2024-04-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2024-04-01'
ORDER BY customer_id;
-- 4, 5

Customers 4 and 5 bought early and went quiet. That "bought before, not since" question is EXCEPT in one line.

Both operators remove duplicates in the result, like UNION. PostgreSQL also offers INTERSECT ALL and EXCEPT ALL that respect duplicate counts, though you will rarely need them.

Reconciling two lists#

EXCEPT only checks one direction. To compare two sources, run it both ways.

-- In the warehouse list but missing from billing
SELECT sku FROM warehouse_items
EXCEPT
SELECT sku FROM billing_items;

-- In billing but missing from the warehouse
SELECT sku FROM billing_items
EXCEPT
SELECT sku FROM warehouse_items;

An empty result from both means the two sets match exactly. This is a quick, readable check after a data migration, and far easier than writing a full outer join.

The rules both queries must follow#

1. Same number of columns.
2. Compatible types, position by position. The first column of both queries must be comparable, then the second, and so on.
3. Column names come from the first query. Later names are ignored.
4. ORDER BY goes at the very end and applies to the entire combined result. It can only refer to output column names or positions.

Violate the first two and PostgreSQL tells you:

ERROR:  each UNION query must have the same number of columns
ERROR:  UNION types integer and text cannot be matched

The quiet failure is matching types that happen to be compatible but mean different things. Stack SELECT name, city on top of SELECT city, name and the database is perfectly happy, since both are text. Your report now has cities in the name column. Always check the positions line up.

How NULL behaves#

When deciding whether two rows are duplicates, set operators treat two NULLs as equal. That is the opposite of =, and it is deliberate.

SELECT NULL UNION SELECT NULL;    -- one row, not two

It makes UNION and EXCEPT more useful for comparing data that has gaps, and it differs from how a join would behave on the same columns.

Precedence#

INTERSECT binds tighter than UNION and EXCEPT. Without parentheses:

SELECT 1 UNION SELECT 2 INTERSECT SELECT 2;

is read as 1 UNION (2 INTERSECT 2), which gives 1 and 2. When mixing operators, add parentheses. Nobody should have to remember the precedence table to read your query.

Common mistakes#

Using UNION when UNION ALL is correct. You pay for a sort and may silently lose legitimate duplicates, such as two identical sales on one day.

Misaligned columns. The database checks types, not meaning.

Sorting inside one branch. ORDER BY in the first query of a UNION is not allowed without parentheses, and would not survive the combine anyway.

Using UNION to patch a bad schema. If you union twelve monthly tables for every report, a partitioned table is the real fix. Module 6 covers it.

Practice#

1. Produce one list of all cities from customers plus the string 'HQ' as an extra row, using UNION ALL. How many rows do you get?
2. List customers who have an order in March 2024 or later, but exclude those with a pending order, using EXCEPT.
3. Find products that were ordered in orders placed before April but not in orders placed from April onward. Use INTERSECT or EXCEPT on product_id.
4. Write the two-direction reconciliation check between SELECT DISTINCT country FROM customers and a hand-written list of countries using VALUES. What does each direction tell you?
5. Predict whether SELECT customer_id, order_id FROM orders UNION SELECT order_id, customer_id FROM orders raises an error. Run it, then explain why the output is wrong even if it does not fail.