INTERSECT, EXCEPT and Column Compatibility

Concept

INTERSECT returns rows present in both result sets. EXCEPT (spelled MINUS in Oracle) returns rows present in the first and absent from the second. Both remove duplicates by default.

Syntax

SELECT col FROM table_a
INTERSECT
SELECT col FROM table_b;

SELECT col FROM table_a
EXCEPT
SELECT col FROM table_b;

Example

-- Cities that host both a department and a customer
SELECT location FROM departments WHERE location IS NOT NULL
INTERSECT
SELECT city FROM customers;

-- Cities with a department but no customer
SELECT location FROM departments WHERE location IS NOT NULL
EXCEPT
SELECT city FROM customers;

Explanation

EXCEPT is directional: swapping the two queries answers the opposite question. INTERSECT is not - the order of the operands makes no difference to the result.

Both are set operations, so they de duplicate. If a city appears in departments three times, INTERSECT returns it once. INTERSECT ALL and EXCEPT ALL preserve multiplicity and exist in PostgreSQL and a few others, but they are rarely needed.

Emulating them where they are missing

-- INTERSECT, emulated with an inner join on distinct values
SELECT DISTINCT d.location
FROM   departments d
JOIN   customers c ON c.city = d.location;

-- INTERSECT, emulated with IN
SELECT DISTINCT location FROM departments
WHERE  location IN (SELECT city FROM customers);

-- EXCEPT, emulated with NOT EXISTS (NULL safe)
SELECT DISTINCT d.location
FROM   departments d
WHERE  d.location IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM customers c WHERE c.city = d.location);

-- EXCEPT, emulated with LEFT JOIN ... IS NULL
SELECT DISTINCT d.location
FROM   departments d
LEFT JOIN customers c ON c.city = d.location
WHERE  c.id IS NULL AND d.location IS NOT NULL;

The emulations are worth knowing even where the real operators exist, because they extend naturally to multi column comparisons and they let you return extra columns, which set operations cannot.

Important rules

• Column count, order and type compatibility rules are identical to UNION.
• INTERSECT and EXCEPT remove duplicates unless the ALL variant is used and supported.
• In standard SQL, INTERSECT binds tighter than UNION and EXCEPT. Use parentheses when mixing them.
• These operations compare whole rows, so NULLs are treated as equal to each other here - unlike in a join condition.
• Set operations cannot return columns that are not being compared; a join or EXISTS can.

Common mistakes

• Writing INTERSECT on MySQL 5.7 or MariaDB 10.2 and getting a syntax error.
• Reversing the operands of EXCEPT and answering the opposite question.
• Expecting EXCEPT to keep duplicate rows.
• Mixing UNION and INTERSECT without parentheses and relying on precedence.

Best practices

• Check the target version before using INTERSECT or EXCEPT in shipped code.
• Prefer NOT EXISTS to EXCEPT when you also need columns from the first table.
• Parenthesise any expression combining more than one set operator.
• Comment which question each half answers - EXCEPT queries are easy to read backwards.

Practice

1. List product categories that appear in both products and in shipped order lines.
2. List customers who have never had a cancelled order, using EXCEPT and again using NOT EXISTS.
3. Rewrite an INTERSECT query for a MySQL 5.7 server.