CODEWITHSUNDEEP
sqlexists
codingknob
codingknob

Reputation: 11660

Check if entry in table A exists in table B

I have a definition table that I know is not being maintained very well, let's call this table A. I have another table (call it table B) that is much smaller and ideally should be a subset of table A but I know that table A is somewhat stale and does not contain new entries that are in Table B.

Note, that tables A and B have different columns.

Table A:
ID, Name, blah, blah, blah, blah

Table B:
ID, Name

I want all rows in Table B such that the ID in Table B does NOT exist in Table A. This does not just match rows in Table A; I want only rows in Table B where the ID does NOT exist at all in Table A.

Upvotes: 116

Views: 372399

Answers (5)

Kai Dong
Kai Dong

Reputation: 111

This also works

SELECT *
FROM tableB
WHERE ID NOT IN (
  SELECT ID FROM tableA
);

Upvotes: 11

Daflanck
Daflanck

Reputation: 46

Or if "NOT EXISTS" are not implemented

SELECT *
FROM   B
WHERE (SELECT count(*)  FROM   A WHERE  A.ID = B.ID) < 1

Upvotes: 0

Danylo Zherebetskyy
Danylo Zherebetskyy

Reputation: 1517

The classical answer that works in almost every environment is

SELECT ID, Name, blah, blah
FROM TableB TB
LEFT JOIN TableA TA
ON TB.ID=TA.ID
WHERE TA.ID IS NULL

sometimes NOT EXISTS may be not implemented (not working).

Upvotes: 43

Abe Miessler
Abe Miessler

Reputation: 85036

If you are set on using EXISTS you can use the below in SQL Server:

SELECT * FROM TableB as b
WHERE NOT EXISTS
(
   SELECT * FROM TableA as a
   WHERE b.id = a.id
)

Upvotes: 15

gdoron
gdoron

Reputation: 150253

SELECT *
FROM   B
WHERE  NOT EXISTS (SELECT 1 
                   FROM   A 
                   WHERE  A.ID = B.ID)

Upvotes: 221

Related Questions