Have Geek Brain

Alternative to NOT EXISTS

I have two tables linked by an id column. Call them table A and table B. My goal is to find all records in table A that do not have a record in table B. For example:

**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, the record with ID = 3 does not exist in table B, so I need a query that will give me record 3 from table A. The way I am doing it now is this 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 to use Left Join where TableB.ID is NULL, it didn't work. Can anyone suggest an alternative?

+2


a source to share


3 answers


Try Not IN 

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


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

+4


a source


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 decision should be more effective than IN()

, and EXISTS()

alternatives. Source here

+1


a source 


SELECT ID 
  FROM Table_A
EXCEPT
SELECT ID 
  FROM Table_B;
0


a source






More articles:


All Articles