Alternative to NOT EXISTS

Question

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?

Solution 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

Solution 2

You can replace it with

SELECT 
  a.ID, 
  a.Value
FROM Table_A AS a
LEFT JOIN Table_B AS b ON a.ID = b.ID     
WHERE b.ID IS NULL

This solution should be more efficient than IN() and EXISTS() alternatives. Source here