SQL Statements should come out the same, but don't [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

SQL NOT IN not working

Hey All,

I have the following two queries:开发者_如何学Python

select distinct ID from db2.tran tr
where ID not in(
select distinct id from db2.tran
join (select distinct id from db1.client) as c on c.id = tr.id)


select distinct id from db2.tran tr
where id not in (select distinct id from db1.client)

Now in my mind, these queries should be equivelant, however, the first one returns rows, and the second one does not. Am i going crazy or is thier logic behind why these two are not coming out the same.

Thanks

---

With your second query

select distinct id
from   db2.tran tr
where  id not in (select distinct id
                  from   db1.client) 

As the sub query brings back NULL values the query as a whole will always return an empty resultset for the reasons described in the comments. (See also SQL and the Snare of Three Valued Logic)

With your first query

select distinct ID
from   db2.tran tr
where  ID not in(select distinct id
                 from   db2.tran
                        join (select distinct id
                              from   db1.client) as c
                          on c.id = tr.id)

One effect of the JOIN condition c.id = tr.id will be to exclude all NULL values from the result of the sub query therefore this issue doesn't arise.

---

we have seen very inconsistent results when using IN/NOT IN statements.

this should also be equivalent:

SELECT
distinct ID 
FROM db2.tran tr
where ID NOT IN (

   SELECT distinct a.id from db2.tran a join db1.client b on (a.id = b.id)

)