CODEWITHSUNDEEP
sqlsql-server-2005
Niklas
Niklas

Reputation: 13155

Problem with "NOT IN" in a simple SQL query

I need to find all id's from the OldTable that doesn't exist in the NewTable
Why won't this query find the id? 

SELECT old_id FROM OldTable WHERE old_id NOT IN (
SELECT id FROM NewTable)

By them selves they return this

--Returns the id 18571
SELECT old_id FROM OldTable WHERE old_id = 18571

 --Returns nothing  
SELECT id FROM NewTable WHERE id = 18571

Am I missing something obvious here?

Both columns are of type int and primary keys.

SOLVED

The id column had null's in them, I was just being ignorant =/

These works: 

SELECT old_id FROM OldTable EXCEPT SELECT id FROM NewTable  

SELECT * FROM old_table ot WHERE NOT EXISTS (
SELECT * FROM new_table nt WHERE nt.id = ot.old_id)

These doesn't work: 

SELECT old_id FROM OldTable LEFT JOIN NewTable ON old_id = id WHERE id IS NULL  

SELECT old_id FROM OldTable WHERE old_id NOT IN (
SELECT id FROM NewTable)

Upvotes: 4

Views: 6900

Answers (5)

onedaywhen
onedaywhen

Reputation: 57093

The difference can be attributed to the presence of nulls.

Consider these two simplified queries, noting the predicate for both is NULL = 1 which evaluates to UNKNOWN which is handled differently by NOT EXISTS and NOT IN respectively: 

SELECT *
  FROM OldTable
 WHERE NULL NOT IN (SELECT 1 FROM OldTable);

 SELECT *
  FROM OldTable
 WHERE NOT EXISTS (SELECT * FROM OldTable WHERE NULL = 1);

The first returns no rows because NOT IN (subquery) evaluated to FALSE.

The first returns all rows because NOT EXISTS (subquery) evaluated to TRUE.

Conclusion: avoid nulls.

Upvotes: 2

user359135
user359135

Reputation: 

select oldtable.id as orginal, newtable.id as new 
from oldtable 
left outer join newtable 
on oldtable.id =newtable.id 
where new is null

AFAIK (and i am not an expert - check my rep ;-) left outer join is a good technique for this.. it will may perform a not in and does not require a sub-select

@onedaywhen kindly points out that this is not always the case e.g. in SQL Server EXISTS() can be more efficient.

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

SELECT * FROM old_table ot
WHERE NOT EXISTS (
   SELECT * FROM new_table nt
   WHERE nt.new_key = ot.old_key
   );

Upvotes: 2

user254875486
user254875486

Reputation: 11240

I don't know why your query doesn't give you the desired result, but I do know that using NOT IN is not very efficient. You would be better of using a joins:

SELECT old_id 
FROM OldTable 
LEFT JOIN NewTable
    ON old_id = id
WHERE id IS NULL

Upvotes: 1

Hari Gillala
Hari Gillala

Reputation: 11926

SELECT old_id FROM OLDTable WHERE id not in (SELECT id from NewTable);

You are using old_id in where condition in the first query and you are using id in second query

Upvotes: 0

Related Questions