What are the ways to find rows in one table that have no matching row in another?
There are three ways, and one of them is dangerous.
The first way is a left join with a test for the missing value. You join the other table on, and then you keep only the rows where the other table gave you nothing.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO customers (id, name) VALUES
(1, 'Ada Lovelace'),
(2, 'Grace Hopper'),
(3, 'Alan Turing');
INSERT INTO orders (id, customer_id, amount) VALUES
(1, 1, 250.00),
(3, 2, 900.00);
-- The customers with no orders.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;
The second way is NOT EXISTS, and it is the safest of the three.
-- The customers with no orders.
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
ORDER BY c.id;
So what does NOT EXISTS do? It asks whether there is a row in the other table that matches. If there is not one, then the condition is true and the customer is kept. The engine can stop at the first match, so it is usually the fastest of the three as well.
The third way is NOT IN, and it is the dangerous one.
-- Do not write this.
SELECT c.id, c.name
FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);
If the subquery ever produces a missing value, then the whole answer comes back empty. The comparison against a missing value is unknown, and unknown is not true, so every row is dropped.
So the rule to remember is this. Use NOT EXISTS when you want the rows with no partner. It is safe with missing values and it is fast.
The next question in this topic puts it to work.