Forgly

SQL to find rows not in another table

SQL to find rows in one table that are not in another — the anti-join, written three ways: NOT EXISTS, NOT IN, and LEFT JOIN / IS NULL.

NOT EXISTS — works everywhere, NULL-safe (recommended)
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

The safest way to find rows not in another table — it never breaks on NULLs and is usually the fastest.

LEFT JOIN / IS NULL — works everywhere
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
NOT IN — simplest, but watch out for NULLs
SELECT c.*
FROM customers c
WHERE c.id NOT IN (
  SELECT customer_id FROM orders
  WHERE customer_id IS NOT NULL
);

NOT IN reads most naturally, but if the subquery returns any NULL it yields zero rows — so filter NULLs out (or prefer NOT EXISTS).

How to find rows in one table that are not in another

To find rows not in another table you use an anti-join: it returns the rows with no counterpart — for example customers who have never placed an order. SQL gives you three ways to write "not in another table": NOT EXISTS, NOT IN, and LEFT JOIN ... IS NULL.

How it works

  • An anti-join returns rows from the first table that have no matching row in the second.
  • LEFT JOIN keeps every customer and attaches matching orders, or NULLs when there are none; WHERE o.id IS NULL then keeps only the customers with no order.
  • NOT EXISTS checks row by row whether any matching order exists and is generally just as fast.
  • NOT IN is the most readable form but returns nothing when the subquery contains a NULL — always exclude NULLs or use NOT EXISTS instead.

Tip

Reach for NOT EXISTS by default: it is NULL-safe and the optimizer treats it the same as the LEFT JOIN / IS NULL anti-join. Only use NOT IN when you are certain the subquery column has no NULLs.

Related SQL queries

Frequently asked questions

How do I find rows in one table that are not in another in SQL?

To find rows not in another table you use an anti-join: it returns the rows with no counterpart — for example customers who have never placed an order. SQL gives you three ways to write "not in another table": NOT EXISTS, NOT IN, and LEFT JOIN ... IS NULL. An anti-join returns rows from the first table that have no matching row in the second. LEFT JOIN keeps every customer and attaches matching orders, or NULLs when there are none; WHERE o.id IS NULL then keeps only the customers with no order. NOT EXISTS checks row by row whether any matching order exists and is generally just as fast. NOT IN is the most readable form but returns nothing when the subquery contains a NULL — always exclude NULLs or use NOT EXISTS instead.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.