INTERSECT and EXCEPT

You have learned UNION for combining result sets. Now let's cover the other two set operations: INTERSECT (find common rows) and EXCEPT (find differences).

Set Operations as Venn Diagrams

     UNION                INTERSECT             EXCEPT (A - B)
  +---------+           +---------+           +---------+
 /     A     \         /     A     \         /  A (kept) \
|  +---------+|       |  +---------+|       |  +---------+|
|  |  both   ||       |  |returned||       |  | removed ||
|  +---------+|       |  +---------+|       |  +---------+|
 \     B     /         \     B     /         \     B     /
  +---------+           +---------+           +---------+
  Everything           Only overlap          A minus B

INTERSECT: Find Common Rows

INTERSECT returns only rows that appear in both result sets:

SELECT columns FROM query_a
INTERSECT
SELECT columns FROM query_b;

Example: People Who Are Both Employees and Customers

SELECT name FROM employees
INTERSECT
SELECT name FROM customers;

This returns names that exist in both the employees table and the customers table. If Alice is both an employee and a customer, her name appears. If Bob is only an employee, he does not.

How INTERSECT Works

employees names:     customers names:      INTERSECT result:
+---------+          +---------+           +---------+
| Alice   |          | Alice   |           | Alice   |
| Bob     |          | Carol   |           +---------+
| Charlie |          | Alice   |
| Diana   |          | Diana   |
+---------+          +---------+

Only Alice (and Diana, if she appears in both) survives.
INTERSECT also deduplicates, like UNION.

INTERSECT ALL

Like UNION ALL, there is an INTERSECT ALL variant that preserves duplicates. If a name appears 3 times in A and 2 times in B, INTERSECT ALL returns it 2 times (the minimum count):

SELECT name FROM employees
INTERSECT ALL
SELECT name FROM customers;

In practice, INTERSECT ALL is rarely used.

Practical Example: Shared Email Domains

Find email domains that exist in both employees and customers:

SELECT SPLIT_PART(email, '@', 2) AS domain
FROM employees
INTERSECT
SELECT SPLIT_PART(email, '@', 2) AS domain
FROM customers;

SPLIT_PART(email, '@', 2) extracts the domain part of the email address.

EXCEPT: Find Differences

EXCEPT returns rows from the first query that do not appear in the second query:

SELECT columns FROM query_a
EXCEPT
SELECT columns FROM query_b;

Example: Employees Who Are Not Customers

SELECT name FROM employees
EXCEPT
SELECT name FROM customers;

This returns employee names that do not appear in the customers table.

Example: Customers Who Are Not Employees

SELECT name FROM customers
EXCEPT
SELECT name FROM employees;

Notice the queries are swapped. This returns customer names that do not appear in the employees table. EXCEPT is not commutative -- the order matters.

Visualizing EXCEPT

A EXCEPT B:                    B EXCEPT A:
employees:   customers:        customers:   employees:
+---------+  +---------+      +---------+   +---------+
| Alice   |  | Alice   |      | Alice   |   | Alice   |
| Bob  *  |  | Carol   |      | Carol * |   | Bob     |
| Charlie*|  | Diana   |      | Diana   |   | Charlie |
| Diana   |  +---------+      +---------+   | Diana   |
+---------+                                  +---------+

A EXCEPT B = {Bob, Charlie}    B EXCEPT A = {Carol}
(* = appears in result)       (* = appears in result)

EXCEPT ALL

EXCEPT ALL preserves duplicates. If a value appears 3 times in A and 1 time in B, EXCEPT ALL returns it 2 times:

SELECT name FROM employees
EXCEPT ALL
SELECT name FROM customers;

Combining Set Operations

You can chain multiple set operations in a single query:

-- Find names exclusive to employees (not in customers)
-- combined with names exclusive to customers (not in employees)
(SELECT name FROM employees
 EXCEPT
 SELECT name FROM customers)

UNION ALL

(SELECT name FROM customers
 EXCEPT
 SELECT name FROM employees);

Use parentheses to control the order of operations when chaining.

Practical Use Cases

Data Validation: Find Missing Records

Check if all departments referenced by employees exist in the departments table:

SELECT DISTINCT department AS missing_department
FROM employees
EXCEPT
SELECT name
FROM departments;

If this returns any rows, there are employees referencing departments that do not exist in the departments table.

Finding Overlap Between Groups

Find cities where both premium and basic customers live:

SELECT city FROM customers WHERE membership = 'premium'
INTERSECT
SELECT city FROM customers WHERE membership = 'basic';

Exclusive Members of Each Group

Find cities with premium customers but no basic customers:

SELECT city FROM customers WHERE membership = 'premium'
EXCEPT
SELECT city FROM customers WHERE membership = 'basic';

Comparing Two Result Sets

Verify that two queries return the same results (useful for testing):

-- If both of these return 0 rows, the queries produce identical results

-- Rows in A but not B
(SELECT name, email FROM employees WHERE department = 'Engineering')
EXCEPT
(SELECT name, email FROM employees WHERE salary > 75000 AND department = 'Engineering');

-- Rows in B but not A
(SELECT name, email FROM employees WHERE salary > 75000 AND department = 'Engineering')
EXCEPT
(SELECT name, email FROM employees WHERE department = 'Engineering');

Set Operations vs Other Approaches

INTERSECT vs INNER JOIN

-- Using INTERSECT
SELECT name FROM employees
INTERSECT
SELECT name FROM customers;

-- Using INNER JOIN (similar result)
SELECT DISTINCT e.name
FROM employees e
INNER JOIN customers c ON e.name = c.name;

Both approaches work, but INTERSECT is cleaner when you only need to compare simple column values.

EXCEPT vs LEFT JOIN with IS NULL

-- Using EXCEPT
SELECT name FROM employees
EXCEPT
SELECT name FROM customers;

-- Using LEFT JOIN (similar result)
SELECT e.name
FROM employees e
LEFT JOIN customers c ON e.name = c.name
WHERE c.name IS NULL;

Again, both work, but EXCEPT is more concise for simple set difference operations.

EXCEPT vs NOT IN

-- Using EXCEPT
SELECT name FROM employees
EXCEPT
SELECT name FROM customers;

-- Using NOT IN
SELECT name FROM employees
WHERE name NOT IN (SELECT name FROM customers);

EXCEPT handles NULLs more safely than NOT IN (recall the NULL trap from the subqueries lesson).

Summary of All Set Operations

Try It Yourself

1. Find names that appear in both the employees and customers tables using INTERSECT.

2. Find customer names that do not belong to any employee using EXCEPT.

3. Find cities where customers live but that are not associated with any department location. Use the customers.city column and departments.location column with EXCEPT.

4. Use a combination of set operations to categorize every unique name from both tables into three groups: "employee only", "customer only", and "both". Hint: use EXCEPT and INTERSECT with UNION ALL and a label column.