Forgly

SQL to find rows not in another table

Return rows from one table that have no match in another (anti-join).

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 EXISTS — works everywhere, NULL-safe
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Prefer NOT EXISTS over NOT IN — NOT IN returns no rows if the subquery contains any NULL.

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

An anti-join finds the rows with no counterpart — for example customers who have never placed an order. LEFT JOIN ... IS NULL and NOT EXISTS both express it.

How it works

  • 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 that got NULLs — i.e. had no order.
  • NOT EXISTS checks row by row whether any matching order exists and is generally just as fast.

Related SQL queries

Frequently asked questions

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

An anti-join finds the rows with no counterpart — for example customers who have never placed an order. LEFT JOIN ... IS NULL and NOT EXISTS both express it. 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 that got NULLs — i.e. had no order. NOT EXISTS checks row by row whether any matching order exists and is generally just as fast.

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.