How to find missing IDs between two lists (and get the SQL for free)

kdkumawat — Sat, 24 Oct 2026 08:18:46 +0000

You have two exports. One is the source of truth (every paying customer in billing), the other is what you're auditing (customers synced to the CRM). The question is always the same: which IDs are in the first list but missing from the second?

Here are three ways to answer it, depending on where the data lives.

1. Both lists are already in the database

Use an anti-join. NOT EXISTS is the safe default because it behaves correctly when the other side contains NULLs (NOT IN does not):

SELECT b.customer_id
FROM billing b
WHERE NOT EXISTS (
  SELECT 1 FROM crm c WHERE c.customer_id = b.customer_id
);

Or, if your database supports it:

SELECT customer_id FROM billing
EXCEPT
SELECT customer_id FROM crm;

2. One list is in a spreadsheet, the other in the database

Turn the pasted list into rows with VALUES, then anti-join against the table:

SELECT v.id
FROM (VALUES ('1001'), ('1002'), ('1003'), ('1004')) AS v(id)
WHERE NOT EXISTS (SELECT 1 FROM crm c WHERE c.customer_id = v.id);

Building that VALUES list by hand for a few thousand IDs is the annoying part.

3. Both lists are just text (CSV exports, Slack pastes, log output)

This is the case I hit most, so I built a tool for it. In Formaty's Compare Lists:

Put the complete list on the left. It's the reference set.

Paste the list you're checking on the right. It can be shorter, longer or contain duplicates.

   1002
   1003
   1005

Read the "Only in left" bucket. Those are the missing IDs. Copy them as SQL:

   id NOT IN ('1001', '1004')

The same bucket can be copied as IN (...), VALUES rows, JSON, CSV or Markdown, and you also get "Only in right", "Common" and "Duplicates".

Delimiters are auto-detected (newlines, commas, semicolons, pipes, JSON arrays), so you can paste raw query output as-is. It runs entirely in your browser in a Web Worker, so 50k+ IDs compare in well under a second and nothing is uploaded.

FAQ

What if my IDs are strings, like UUIDs? Comparison is exact, and the SQL export quotes values correctly.

Can I compare more than two lists? Chain it: compare A vs B, then compare the result with C.

Formaty is free and open source (GitHub). Besides list compare it does JSON diff, JSON to TypeScript/Zod/SQL, cURL to code and more. Here's a 60-second demo. Feedback welcome in the comments.

DEV Community: kdkumawat

How to find missing IDs between two lists (and get the SQL for free)

1. Both lists are already in the database

2. One list is in a spreadsheet, the other in the database

3. Both lists are just text (CSV exports, Slack pastes, log output)

FAQ