nlqdb

Solve · Analysts and PMs

How do I find rows in one table with no match in another (anti-join) in SQL?

If you need the rows in one table with no match in another — customers who never placed an order — ask in plain English instead of hand-writing an anti-join. nlqdb compiles the `LEFT JOIN ... WHERE b.id IS NULL` (or `NOT EXISTS`), runs it in Postgres, and shows the SQL so you can dodge the `NOT IN` NULL trap.

Analysts ask 'who's missing' all the time — customers who never ordered, products never sold, users with no login this month, invoices with no payment. It's a set difference, and it's the query people get silently wrong. The tempting `WHERE id NOT IN (SELECT customer_id FROM orders)` returns zero rows the instant that subquery contains a single NULL, because `NOT IN` compares against NULL as unknown and the whole predicate collapses. The two correct shapes are a `LEFT JOIN` with `WHERE b.key IS NULL` (keep the left rows that found no partner) or `NOT EXISTS` (a correlated anti-join that's NULL-safe by construction) — and knowing which one, and why `NOT IN` betrayed you, is the whole difficulty.

The snippet that solves it.

> customers who have never placed an order

What nlqdb does for this

  • Ask 'customers who never placed an order'; nlqdb compiles the NULL-safe anti-join `LEFT JOIN orders WHERE orders.id IS NULL` and runs it.
  • The compiled SQL avoids the `NOT IN (subquery)` shape, so a NULL in the inner table can't silently collapse the result to zero rows.
  • Every answer returns the missing rows plus the SQL under a trace toggle — confirm it's `IS NULL` / `NOT EXISTS`, not `NOT IN`.
  • Repeated 'who's missing' checks hit the plan cache — content-addressed on `(goal-fingerprint, schema-hash)` — so the same query returns in single-digit ms.

Drop into any HTML page

<nlq-data goal="customers who have never placed an order"></nlq-data>

The canonical 'who's missing' anti-join you'd otherwise hand-write — and get bitten by `NOT IN` on — is one English goal here, with the SQL shown so you can confirm it's the NULL-safe shape.

What this replaces

What nlqdb doesn't try to do here

  • `LEFT JOIN ... IS NULL` and `NOT EXISTS` are equivalent for a plain anti-join, but a `LEFT JOIN` can multiply rows if the join key isn't unique. nlqdb picks one from your English and shows it — it doesn't guarantee your keys are unique.
  • It reports the missing rows with a read-only SELECT — it's not a monitor that alerts you when a new row goes unmatched. A scheduled job or your own alerting owns that.
  • The public `<nlq-data>` embed is read-scoped — it finds the unmatched rows, it doesn't write. No write key belongs in client HTML; loading data goes through the SDK or `POST /v1/run`.

Questions buyers ask

How do I find rows in one table with no match in another in SQL?
Ask in plain English — 'customers who have never placed an order.' nlqdb compiles an anti-join: `LEFT JOIN orders ON ... WHERE orders.id IS NULL` keeps the customers that found no matching order, or the equivalent `NOT EXISTS (SELECT 1 FROM orders WHERE ...)`. It runs the query and shows the SQL, so you can confirm it used a NULL-safe shape rather than `NOT IN`.
Why does NOT IN return no rows in SQL?
Because of a NULL in the subquery. `id NOT IN (SELECT customer_id FROM orders)` is true only when `id` differs from every returned value — but if one `customer_id` is NULL, the comparison is 'unknown', so the predicate is never true and you get zero rows. Use `NOT EXISTS` or a `LEFT JOIN ... IS NULL` instead; nlqdb compiles the NULL-safe form and shows it in the SQL.
What's the difference between LEFT JOIN IS NULL and NOT EXISTS?
For a plain anti-join they return the same rows. `NOT EXISTS` is a correlated subquery that stops at the first match and is NULL-safe by construction. `LEFT JOIN ... WHERE b.key IS NULL` keeps every left row that found no partner — but if the join key isn't unique it can multiply rows before the filter. nlqdb picks one from your phrasing and shows the clause in the SQL.
Can I use EXCEPT instead of an anti-join to find rows with no match?
Yes, for the 'which keys are missing' shape. `EXCEPT` is set difference — NULL-safe (unlike `NOT IN`) and terse. The catch: it returns only the columns you project and de-duplicates them, so you get the distinct missing keys, not the full unmatched rows with their other columns. When you want the whole row, the `LEFT JOIN ... IS NULL` / `NOT EXISTS` anti-join is the right shape. nlqdb picks between them from your phrasing and shows the SQL.
Is NOT EXISTS or LEFT JOIN faster for an anti-join?
In modern Postgres, neither — the planner turns both `NOT EXISTS` and `LEFT JOIN ... IS NULL` into the same anti-join, so they share a plan. The one to avoid is `NOT IN (subquery)`: on a nullable column it can't become an anti-join at all — the correctness trap and the slow path. An index on the inner table's join key is what actually moves the time. nlqdb compiles the anti-join shapes, never `NOT IN`, and shows the SQL.
Can I run an anti-join on a Postgres database I already run?
Yes — connect it with the signed-in BYO connect verb (`nlq db connect`; see /solve/query-existing-postgres-in-natural-language) and ask 'rows with no match' in place, no ETL into a separate store. The honest limits: BYO connect is signed-in only (not the public embed), and nlqdb returns the unmatched rows read-only — it doesn't persist the result set for you.

Where this pain shows up in public

Enduring discussion hubs where you can verify the theme without taking our word for it. We don't quote individual posts; we cite search-result and subreddit URLs that stay live as new threads land.

Try nlqdb in 30 seconds

No sign-in. The anonymous database lasts 72 hours; adopt it with one click if you keep it.

Start with a goal →

Further reading: NOT IN returned zero rows. It wasn't your data — it was one NULL.

Looking at this from a different angle? Browse all solve pages or browse competitor comparisons.