SQL Statement Question - How to retrieve records of a table where the primary key does not exist in another table as a foreign key.

Question

I have a table (TableA) where the primary key exists as a foreign key on another table (TableB). I am trying to write a SQL query (I am using SQL Server 2005) that will find all of the records in TableA where the primary key does not exist in TableB as a foreign key (we'll say joining on TableAID). I suspect it will use an outer join, but I have been struggling. Can anyone help a SQL newb out?

Answer 1

SELECT A.*
FROM TableA AS A
LEFT JOIN TableB AS B ON (A.ID = B.TableAID)
WHERE B.TableAID IS NULL

Answer 2

You can use NOT EXISTS

SELECT columnlist
FROM TableA
WHERE NOT EXISTS(
   SELECT * FROM TableB
   WHERE TableB.column = TableA.column)

Answer 3

Select * from TableA 
where 
 TableA.TableAID not in (select TableB.TableAID from TableB)

Answer 4

This is nearly natural language in SQL ("WHERE NOT EXISTS"):

SELECT A.*
FROM TableA AS A
WHERE NOT EXISTS ( SELECT 1
                   FROM TableB
                   WHERE TableA.ID = B.TableAID
                 )