Alternative to NOT EXISTS

Question

Hi all,

I have two tables linked by an ID column. Let's call them Table A and table B. My goal is to find all the records in table A that have no record in table B. For instance:
Table A:
ID----Value
1-----value1
2-----value2
3-----value3
4-----value4

Table B
ID----Value
1-----x
2-----y
4-----z
4-----l

As you can see, record with ID = 3 does not exist in table B, so I want a query that will give me record 3 from table A. The way I am currently doing this is by saying AND NOT EXISTS (SELECT ID FROM TableB where TableB.ID = TableA.ID), but since the tables are huge, the performance on this is terrible. Also, when I tried using a Left Join where TableB.ID is null, it didn't work. Can anyone suggest an alternative?

Answer 1

Try Not IN

AND tablea.id NOT In (SELECT ID FROM TableB)

check more http://www.java2s.com/Code/SQLServer/Select-Query/NOTIN.htm