SQL INTERSECT and EXCEPT: Shared and Missing Set Values

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

INTERSECT computes the common selected values. Selecting customers.id and intersecting sales.customer_id produces the identified purchaser ids one, two and three. Repeated sales for the same customer do not repeat that id in the ordinary distinct result. EXCEPT is directional: customer ids EXCEPT purchaser ids returns four, while reversing the branches answers a different question about references absent from the customer list.

Both inputs must produce the same number of columns in corresponding compatible types. The selected row is the comparison unit, so selecting id and name requires the same pair on both sides. A name-only comparison can conflate two people with the same name. Use the entity identity when the question is membership of entities, and project descriptive labels afterwards through an appropriate join if needed.

Ordinary INTERSECT and EXCEPT remove duplicates and treat NULL values as equal for set comparison. This is distinct from ordinary NULL equality in WHERE. The anonymous customer_id does not remove customer four in the EXCEPT example, because NULL is a different selected value from four. This contrasts with a NOT IN predicate whose membership set includes NULL and can make the nonmatching comparison unknown.

ALL variants preserve multiplicities according to set arithmetic where supported, but support differs across engines. Compound-query precedence can also differ: INTERSECT may bind more tightly than UNION or EXCEPT. Parenthesise or use named CTE stages when combining several operations rather than relying on a reader to remember local precedence rules. Add one final ORDER BY for presentation. For a relationship with complicated eligibility conditions, a correlated EXISTS or NOT EXISTS can be easier to extend than a projected set comparison.

PostgreSQL, SQL Server and SQLite support INTERSECT and EXCEPT. MySQL added these operations in version 8.0.31; older versions require another formulation. ALL variants are not shared by every engine, so the lesson uses ordinary distinct forms. SQL Server does not support EXCEPT ALL or INTERSECT ALL.

Check the engine-specific syntax and version requirements in MySQL set operations, PostgreSQL set operations.

Return the identified customer ids that appear in both customers and sales. Use INTERSECT and sort the result.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id AS customer_id FROM customers
INTERSECT SELECT customer_id FROM sales
ORDER BY customer_id;

customer_id
1
2
3

The common selected values are the three customer identities with sales. Their repeated occurrences in sales do not repeat the result. NULL is absent from the non-NULL customer primary-key set, so the anonymous reference is not part of the intersection.

Reverse the example’s EXCEPT branches and predict a NULL customer reference from sales rather than Dev’s id. Add WHERE customer_id IS NOT NULL to that branch and predict an empty difference, since all identified references are valid. This separates absent optional ownership from invalid parent membership and illustrates why set direction and projected fields define the question.