Retrive Records Form One Table as long as they do not exist in Another table T-SQL

Question

Hi,

I need to wite a query that will retrieve the records from Table A , provided that the key in Table A does not exist in Table B.

Any help will be appreciated.

Thanks

Answer 1

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

Answer 2

Assuming: TableA's Id = Id TableB's Id = Id

select * from TableA ta where ta.Id not in (select Id from TableB)

Answer 3

select a.* 
from
    tableA a
    left join tableB b
     ON a.id = b.id
where
    b.id is null

Answer 4

Use a left join. The DB tries to map datasets from TableB to TableA using the id fields. If there is no fitting data set available in TableB, the TableB data gets NULL. Now you just have to check for TableB.id to be NULL.

SELECT TableA.* FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id WHERE TableB.id IS NULL

Answer 5

SELECT * FROM TableA
WHERE NOT Exists(SELECT * FROM TableB WHERE id=TableA.id)

also works and it's almost self documenting...

Answer 6

None of the above solutions would work if the key comprises of multiple columns.

If the tables have compound primary keys, you'll have to use a "NOT EXISTS" clause similar to the one below.

SELECT * 
  FROM TableA AS a 
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM TableB b 
                    WHERE b.id1 = a.id1 
                          AND b.id2 = a.id2 
                          AND b.id3 = a.id3
                  );