DEV Community

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

Posted on Originally published at formaty.dev AI-assisted

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

#sql #database #webdev #productivity

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
);
Enter fullscreen mode Exit fullscreen mode

Or, if your database supports it:

SELECT customer_id FROM billing
EXCEPT
SELECT customer_id FROM crm;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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:

  1. Put the complete list on the left. It's the reference set. 
   1001
   1002
   1003
   1004
Enter fullscreen mode Exit fullscreen mode
  1. Paste the list you're checking on the right. It can be shorter, longer or contain duplicates. 
   1002
   1003
   1005
Enter fullscreen mode Exit fullscreen mode
  1. Read the "Only in left" bucket. Those are the missing IDs. Copy them as SQL: 
   id NOT IN ('1001', '1004')
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)