CODEWITHSUNDEEP
sqloracle-database
Brent Underwood
Brent Underwood

Reputation: 103

'in()' works as expected, but 'not in()' does not

I have a two column table (n and p), where half the n values exist in p and the other half do not. The following code returns 'NULL' instead of returning the half of the n values which are not in p.

select  n
from bst
where n 
not in 
(
    select p from bst
)
order by n
;

But when the keyword 'not' is removed, everything works as expected. Why is this code returning no values?

Upvotes: 1

Views: 83

Answers (3)

SevincQurbanova
SevincQurbanova

Reputation: 366

select n from bst 
where n not in
( select coalesce(p,0)     from bst )
order by n ;

Coalesce will return 0 if p is null. You can write another value in place of 0. Write value that cant be in n.

Upvotes: 0

ArtBajji
ArtBajji

Reputation: 960

create table bst(n number, p number);

insert into bst values(null,null);
insert into bst values(2,2);
insert into bst values(null,3);
insert into bst values(4,null);
insert into bst values(5,5);
insert into bst values(11,6);
insert into bst values(null,7);
insert into bst values(13,8);
insert into bst values(14,null);
insert into bst values(15,10);

commit;

select * from bst;

Output:

N   P
 -   - 
2   2
 -  3
4    - 
5   5
11  6
 -  7
13  8
14   - 
15  10

select n from bst where n not in (select p from bst);

Output:

No rows fetched

select n from bst where n not in (select p from bst where p is not null);

Output:

N
14
15
11
4
13

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I strongly recommend using not exists instead of not in with a subquery:

select bst.n
from bst
where not exists select p from bst bst2 where bst2.p = bst.n)
order by bst.n

Why? Because the two are not equivalent. If any value of p is NULL, then NOT IN returns no rows at all. NOT EXISTS has more expected behavior.

Of course, you could fix this problem by filtering in the subquery:

select bst.n
from bst
where bst.n not in (select bst2.p from bst bst2)
order by bst.n;

But I think it is easier just to remember to use not exists rather than not in.

Upvotes: 1

Related Questions