UNION, INTERSECT and EXCEPT

A join puts tables side by side. A set operation stacks two query results on top of each other, or compares them row against row.

• A UNION B — rows in either, duplicates removed.
• A UNION ALL B — rows in either, everything kept.
• A INTERSECT B — rows in both.
• A EXCEPT B — rows in A that are not in B (Oracle has long spelt it MINUS).

UNION, INTERSECT and EXCEPT return distinct rows; each has an ALL form that keeps duplicates, though UNION ALL is the only one every major engine supports.

The rules for combining

Both queries must return the same number of columns, and columns in the same position must have compatible types. Columns are matched by position, never by name; the result takes its column names from the first query. PostgreSQL reports a width mismatch as "each UNION query must have the same number of columns".

1CREATE TABLE customers (id int PRIMARY KEY, email text);2CREATE TABLE subscribers (email text NOT NULL);3INSERT INTO customers VALUES4  (1, 'nino@example.com'), (2, 'ana@example.com'), (3, NULL);5INSERT INTO subscribers VALUES6  ('ana@example.com'), ('luka@example.com'), ('luka@example.com');

1SELECT2  (SELECT COUNT(*) FROM (SELECT email FROM customers3                         UNION ALL4                         SELECT email FROM subscribers) AS u) AS union_all,5  (SELECT COUNT(*) FROM (SELECT email FROM customers6                         UNION7                         SELECT email FROM subscribers) AS u) AS union_distinct,8  (SELECT COUNT(*) FROM (SELECT email FROM customers9                         INTERSECT10                         SELECT email FROM subscribers) AS i) AS in_both,11  (SELECT COUNT(*) FROM (SELECT email FROM customers12                         EXCEPT13                         SELECT email FROM subscribers) AS e) AS only_customers;

Six rows stacked; four distinct (NULL included as one of them); one email in both lists; two in the first but not the second. UNION's de-duplication is real work — a sort or a hash over the whole result — so when the inputs cannot overlap, or you want the duplicates, write UNION ALL.

NULL matches NULL here

The NULL lesson showed that NULL = NULL is unknown, so a join on two NULLs finds nothing. Set operations compare rows the way DISTINCT does instead, and for them two NULLs are the same:

1SELECT 'intersect' AS method, COUNT(*) AS rows_returned2FROM (SELECT CAST(NULL AS text) AS v3      INTERSECT4      SELECT CAST(NULL AS text)) AS i5UNION ALL6SELECT 'join on =', COUNT(*)7FROM (SELECT CAST(NULL AS text) AS v) AS a8JOIN (SELECT CAST(NULL AS text) AS v) AS b ON a.v = b.v;

That is why EXCEPT can be a cleaner anti-join than NOT IN: a NULL on either side does not poison the comparison.

Ordering and precedence

An ORDER BY at the end sorts the combined result, and it may only name the result's columns — the first query's names or positions. In standard SQL and PostgreSQL, INTERSECT binds tighter than UNION and EXCEPT, which are applied left to right — so SELECT 1 UNION SELECT 2 INTERSECT SELECT 3 returns just 1. Not every engine follows that rule, so add parentheses when you mix them, and the reader will thank you.

SELECT email FROM customers
UNION
SELECT email FROM subscribers
EXCEPT
SELECT email FROM unsubscribed
ORDER BY email;

Common mistakes

• UNION where UNION ALL was meant. Two identical sales rows from different days collapse into one, and a total built on top is short.
• Columns in a different order in each branch. They are matched by position, so name, email on top of email, name silently produces a column of mixed values whenever both are text.
• Forgetting that survives. A nullable column in one branch puts one row in a result, as the counts example shows.