How Do You Find Non-Matching Rows?

There are four, and they are not equally safe. The LEFT JOIN with a NULL check finds rows where the outer join produced no partner. NOT EXISTS asks the same question with a correlated subquery. NOT IN is a set-membership test with a well-known NULL trap. EXCEPT is the set-difference operator and is the least flexible of the four.

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'),
  (4, 'Katherine Johnson');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

-- Anti-join with LEFT JOIN.
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

-- Anti-join with NOT EXISTS.
SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
)
ORDER BY c.id;

Both queries return the same two rows: Alan Turing and Katherine Johnson, the customers with no orders.

The LEFT JOIN version keeps every left row and then discards the ones where the join failed. The critical detail is that the NULL check has to be on a column from the right-hand table, and it has to use IS NULL rather than an equality against a sentinel value — and it must not be combined with a WHERE predicate on that same right-hand column, because any such predicate turns the outer join back into an inner one.

NOT EXISTS states the same requirement directly and is the safer default, because EXISTS only ever returns TRUE or FALSE. It also reads better when the subquery needs a different correlation or an extra condition.

NOT IN is where the trap lives. If the subquery can return a NULL — and it can, whenever the joined column is nullable — then NOT IN evaluates to UNKNOWN for every row, and the query returns nothing at all. The fix is to make the subquery column NOT NULL, or to use NOT EXISTS instead. EXCEPT works but requires the two branches to have identical column lists, which makes it awkward for anything beyond a simple id difference.

The same anti-join pattern, applied to the product catalogue. The product id is the thing that has to appear in the order lines, and the product that never appears is the one we want.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE order_lines (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty INTEGER NOT NULL
);

INSERT INTO products (id, name) VALUES
  (1, 'Mechanical Keyboard'),
  (2, 'USB-C Hub'),
  (3, '27-inch Monitor'),
  (4, 'Ergonomic Mouse');

INSERT INTO order_lines (id, order_id, product_id, qty) VALUES
  (1, 500, 1, 2),
  (2, 500, 2, 3),
  (3, 501, 1, 1);

SELECT p.id, p.name
FROM products AS p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_lines AS ol
  WHERE ol.product_id = p.id
)
ORDER BY p.id;

Two rows come back: the 27-inch Monitor and the Ergonomic Mouse. The keyboard was ordered twice and the hub once, so neither appears.

This is the query that decides what stays in the catalogue, so it is worth being precise about a few things. It finds products with no order lines at all, which is not quite the same as products with no sales — a product ordered twice and then cancelled still counts as ordered here. If the business question is "never sold", you need to filter the order lines by status inside the subquery, and then you have to decide what a cancelled order means for the answer.

It also finds products that were never ordered, but it will not find products that were never uploaded to the catalogue in the first place. If you want the reverse gap — products that appear in the order lines but not in the catalogue — you flip the join direction, and that query is often the more useful one, because it is finding broken data rather than a business fact.

One performance note: NOT EXISTS is usually the fastest form here, because the engine can stop at the first matching order line. The LEFT JOIN version has to produce the row and then discard it, which involves more work per left row. With an index on order_lines(product_id), both forms are fast, and the difference between them shrinks.