select a value where it doesn't exist in another table

Question

I have two tables

Table A:

ID 1 2 3 4 

Table B:

ID 1 2 3 

I have two requests:

• I want to select all rows in table A that table B doesn't have, which in this case is row 4.
• I want to delete all rows that table B doesn't have.

I am using SQL Server 2000.

Answer 1

You could use NOT IN:

SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B) 

However, meanwhile i prefer NOT EXISTS:

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

There are other options as well, this article explains all advantages and disadvantages very well:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

Answer 2

For your first question there are at least three common methods to choose from:

• NOT EXISTS
• NOT IN
• LEFT JOIN

The SQL looks like this:

SELECT * FROM TableA WHERE NOT EXISTS (     SELECT NULL     FROM TableB     WHERE TableB.ID = TableA.ID )  SELECT * FROM TableA WHERE ID NOT IN (     SELECT ID FROM TableB )  SELECT TableA.* FROM TableA  LEFT JOIN TableB ON TableA.ID = TableB.ID WHERE TableB.ID IS NULL 

Depending on which database you are using, the performance of each can vary. For SQL Server (not nullable columns):

NOT EXISTS and NOT IN predicates are the best way to search for missing values, as long as both columns in question are NOT NULL.