Check if entry in table A exists in table B

sql

not-exists

left-join

not-in

byAnton Shumikhin·Aug 25, 2024

To verify if an entry in Table A exists in Table B, use a JOIN:

SELECT A.*
FROM TableA A
JOIN TableB B ON A.id = B.id; --It's like a reunion party. We only want the folks who are in both guest lists

This returns only entries from Table A with matching ids in Table B.

Let's now flip the tables. Curious if entries in Table B are missing in Table A? We have to query differently.

Step into the void with NOT EXISTS

To spot entries in Table B that don't exist in Table A, the NOT EXISTS clause is your SQL Sherlock Holmes:

SELECT B.*
FROM TableB B
WHERE NOT EXISTS (
  SELECT 1
  FROM TableA A
  WHERE A.id = B.id
); -- "I'm not the ID you're looking for"-- Non-Jedi ID trick

This query spills all the beans, pulling all rows from Table B where the ID does not have a twin in Table A.

To other SQL realms and...oops, NULL?

Rummaging for non-existing entries can take you on tangents, with each road leading to a different cave with its own goblins and goodies.

The LEFT JOIN and NULL junction

Another path lies adorned with LEFT JOIN and lit with NULL checks on the ID column from Table A :

SELECT B.*
FROM TableB B
LEFT JOIN TableA A ON B.id = A.id
WHERE A.id IS NULL; --"Any NULLs? Oh, found you!"

This scoops out entries from Table B that couldn't find their lost twin in Table A.

Consistency, NOT IN, and a spoonful of caution!

Well, say hello to NOT IN. It can sit in for its NOT EXISTS cousin, but beware- it might pull a fast one with NULL values!

SELECT B.*
FROM TableB B
WHERE B.id NOT IN (SELECT A.id FROM TableA A); -- Mind the gap! NULL values can send you off the rails.

This stakeout needs an ally. Keep Table A updated like your Netflix subscription to ensure data integrity during this stakeout.

The ripple effects of refresh cycles

You must remember that Table B might harbor entries that haven't found a room yet in Table A. Lousy synchronization or update delays might mess your headcount.

The syntax savior

It's SQL queries, not sandwich making- misplaced commas and incorrect aliases do cause havoc, not an extra bite!

Count'em all!

Want just the numbers, not the gory details? See how many entries are playing hide and seek:

SELECT COUNT(*)
FROM TableB B
WHERE NOT EXISTS (
  SELECT 1
  FROM TableA A
  WHERE A.id = B.id
); -- It's not an Easter egg hunt; we just need a headcount!