Note: Since someone claimed that the external link is dead in Sushant Butta's answer I've posted the content here as a separate answer.
Beware of NULLS.
Today I came across a very strange behaviour of query while using IN and NOT IN
operators. Actually I wanted to compare two tables and find out whether a value from table b
existed in table a
or not and find out its behavior if the column containsnull
values. So I just created an environment to test this behavior.
We will create table table_a
.
SQL> create table table_a ( a number);
Table created.
We will create table table_b
.
SQL> create table table_b ( b number);
Table created.
Insert some values into table_a
.
SQL> insert into table_a values (1);
1 row created.
SQL> insert into table_a values (2);
1 row created.
SQL> insert into table_a values (3);
1 row created.
Insert some values into table_b
.
SQL> insert into table_b values(4);
1 row created.
SQL> insert into table_b values(3);
1 row created.
Now we will execute a query to check the existence of a value in table_a
by checking its value from table_b
using IN
operator.
SQL> select * from table_a where a in (select * from table_b);
A
----------
3
Execute below query to check the non existence.
SQL> select * from table_a where a not in (select * from table_b);
A
----------
1
2
The output came as expected. Now we will insert a null
value in the table table_b
and see how the above two queries behave.
SQL> insert into table_b values(null);
1 row created.
SQL> select * from table_a where a in (select * from table_b);
A
----------
3
SQL> select * from table_a where a not in (select * from table_b);
no rows selected
The first query behaved as expected but what happened to the second query? Why didn't we get any output, what should have happened? Is there any difference in the query? No.
The change is in the data of table table_b
. We have introduced a null
value in the table. But how come it's behaving like this? Let's split the two queries into "AND"
and "OR"
operator.
The first query will be handled internally something like this. So a null
will not create a problem here as my first two operands will either evaluate to true
or false
. But my third operand a = null
will neither evaluate to true
nor false
. It will evaluate to null
only.
select * from table_a whara a = 3 or a = 4 or a = null;
a = 3 is either true or false
a = 4 is either true or false
a = null is null
The second query will be handled as below. Since we are using an "AND"
operator and anything other than true
in any of the operand will not give me any output.
select * from table_a whara a <> 3 and a <> 4 and a <> null;
a <> 3 is either true or false
a <> 4 is either true or false
a <> null is null
So how do we handle this? We will pick all the not null
values from table table_b
while using NOT IN
operator.
SQL> select * from table_a where a not in (select * from table_b where b is not null);
A
----------
1
2
So always be careful about NULL
values in the column while using NOT IN
operator.
Beware of NULL!!