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

Question

Possible Duplicate:
What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

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

see http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null/2246793#2246793 for all the options listed and the performance implications — kristof, Jan 31 '11 at 16:12

score 8 · Answer 1 · answered Nov 11 '08 at 12:44

8

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

answered Nov 11 '08 at 12:44

kristof

52,923
24
87
110

+1 - According to the powers that be from Microsoft, outer joining is faster than in or where not exists, at least on SQL Server 2000. I don't know if this has changed on SQL Server 2005. – ConcernedOfTunbridgeWells Nov 11 '08 at 13:10
+1 - Although I think that the accepted answer can be easier to think about, from my experience, this SQL has proved much faster for large data volumes. – Russ Cam Nov 11 '08 at 13:29

score 4 · Answer 2 · edited Sep 23 '09 at 08:30

4

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
                  );

edited Sep 23 '09 at 08:30

onedaywhen

55,269
12
100
138

answered Sep 23 '09 at 06:55

Rahul

12,886
13
57
62

+1 This constructs best matches the natural language used in the question. – onedaywhen Sep 23 '09 at 08:31

score 4 · Accepted Answer · answered Nov 11 '08 at 12:42

4

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

answered Nov 11 '08 at 12:42

Galwegian

41,475
16
112
158

Note that this can be slow on large data volumes and the solution described by kristof below is more efficient. – ConcernedOfTunbridgeWells Nov 11 '08 at 13:11

score 2 · Answer 4 · answered Nov 11 '08 at 12:48

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

score 0 · Answer 5 · answered Nov 11 '08 at 12:43

0

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

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

answered Nov 11 '08 at 12:43

CubanX

5,176
2
29
44

score 0 · Answer 6 · answered Nov 11 '08 at 13:23

0

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

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

answered Nov 11 '08 at 13:23

Kaniu

454
2
5

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

6 Answers6