Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Abhishekh.

Asked: March 29, 2016 - 10:27 am UTC

Last updated: July 19, 2019 - 2:09 pm UTC

Version: Oracle Database 11g 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Team Tom,

i have two tables; create table racer(id number,rname varchar2(15)) and data is
INSERT INTO racer VALUES (1,'gendry');
INSERT INTO racer VALUES (2,'arya');
INSERT INTO racer VALUES (3,'drogo');
INSERT INTO racer VALUES (4,'snow');

create table event(eid number,ename varchar2(15),winid number) and data is
INSERT INTO EVENT VALUES (1,'10race',2);
INSERT INTO EVENT VALUES (2,'21race',3);
insert into event values (3,'50race',null);

if i use the query select *From racer where id in (select winid from EVENT) i get two records
2 arya
3 drogo

but for the query select *From racer where id not in (select winid from EVENT) it returns no records

would you please explain this behavior ..

and Chris said...

Thanks for a complete test case!

Remember:

Null is neither equal to nor not equal to anything!

With not in, Oracle compares the column to all values in the subquery. If any of the values are null the whole expression will be null, so return nothing.

See:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684

and

http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/

I'm guessing what you really want for your not in example is:

select * From racer where not exists (
  select null from EVENT where id = winid
);

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Confusion got cleared

Abhishekh KT, March 29, 2016 - 4:41 pm UTC

Good explanation.. and thanks for the links too...
Chris Saxon
March 30, 2016 - 1:10 am UTC

Glad we could help

Null and 1 in not exists

A reader, July 19, 2019 - 6:41 am UTC

what if we write this

select * From racer where not exists (
select 1 from EVENT where id = winid
);

instead of
select * From racer where not exists (
select null from EVENT where id = winid
);

Chris Saxon
July 19, 2019 - 2:09 pm UTC

There's no real difference; I prefer null to show you're not really selecting anything.