CODEWITHSUNDEEP
sqldatabaseoracle-database
Mycotina
Mycotina

Reputation: 521

Equivalent Query Produce Different Result

I believe the following queries are equivalent and should produce the same result :

select CITY from TBL_X
where CITY not in (
  select CITY from TBL_X
  where CITY in (
    select CITY 
    from TBL_Y
  )
)


select CITY 
from TBL_X
where CITY not in (
  select CITY 
  from TBL_Y
);

But the first query produce 10 rows of data while the second produce 0 row of data. Is there any logical explanation for this?

Upvotes: 0

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Don't use not in with a subquery. It returns no rows when any value returned by the subquery is NULL.

Instead, use not exists:

select x.CITY 
from TBL_X x
where not exists (select 1
                  from tbl_y y
                  where y.CITY = x.CITY 
                 );

Your queries are not equivalent, in the same way that these are not opposites:

where x = y
where not (x = y)

Both of these clauses filter out NULL values.

Upvotes: 4

Related Questions