Author 1 name: Dhivakar
Author 1 Email: firstname.lastname@example.org
Date written: 20th May 2003
Oracle version(s): 8i – 9.2
When an SQL statement contains a not in subquery to retrieve selected rows in the where clause and it reports "No rows returned" even though there are some records that appear to be suitable, it is not a bug. Read on...
The main reason behind returning no rows is because the subquery may return a null. If the subquery returns a null then the query does not return a value when it uses a "not in" clause. This is not a bug but a feature.
Let us consider a table "Table1" containing a column "A"
Create table Table1 (A number);
Insert into Table1 Values(1);
Insert into Table1 Values(2);
Let us consider another table "Table2" Containing a column "A"
Create table Table2 (A number);
Insert into Table2 Values(1);
Insert into Table2 Values(Null);
Table2 contains a Null value for the Column "A"
If I write a Query with a Not in Clause using a Subquery of Table2 will lead to No rows even though rows can be returned.
Where A not in (
Select A from Table2
Even though "2" is not in Table2 but in Table1, returns Nothing as Table2 Subquery involves a Null in the "Not in" clause.
I thought I would try to expand on Dhivakar's explanation and with the help of a bit of basic logic show exactly why no rows are returned. Oracle has three logical states. TRUE, FALSE & NULL. TRUE and FALSE work in the standard logical way, NULL however can be tricky. For example: NOT NULL implies NULL (or UNKNOWN) and TRUE AND NULL implies NULL (or UNKNOWN). However, FALSE AND NULL implies FALSE and TRUE OR NULL implies TRUE (standard short circuit evaluation).
I've used the following symbols throughout this explanation:
! - logical NOT
& - logical AND
| - logical OR
=> - Implies
T - True
F - False
N - Null (or Unknown)
Assume that we have three tables:
create table A(r number);
insert into A values(1);
insert into A values(2);
create table B(r number);
insert into B values(1);
insert into B values(3);
create table C(r number);
insert into C values(1);
insert into C values(NULL);
Firstly let's look at the case where there are no NULLs
involved so we can understand why rows are returned. Let's look at the
following SQL query:
where r not in (select r from B);
Oracle will only returns rows from table A when the where clause evaluates to TRUE. Let's look at each row in table A in turn:
A(r=1) => !(1=1 | 1=3) => !(T | F) => !T & !F => F & T => F
What we saying here is that r=1 will not be returned because the where clause evaluates to FALSE (DeMorgans law was used to turn !(T | F) into !T & !F). Now let's look at the next row:
A(r=2) => !(2=1 | 2=3) => !(F | F) => !F & !F => T & T => T
Therefore r=2 will be returned because the where clause evaluates to TRUE.
Now let's look at the case where NULL's are
where r not in (select r from C);
Again, let's look at each row in table A in turn:
A(r=1) => !(1=1 | 1=N) => !(T | N) => !T & !N => F & N => F
The where clause evaluates to FALSE so r=1 will not be returned. We expect this because 1 does appear in table C. Now the next row:
A(r=2) => !(2=1 | 2=N) => !(F | N) => !F & !N => T & N => N
Remember TRUE AND NULL implies NULL. Also remember that Oracle will only return a row if the where clause evaluates to TRUE. In this case the where clause evaluates to NULL so r=2 will not be returned.
We can simplify this further and say that whenever there is a NULL involved the where clause will always evaluate to NULL and therefore no rows will be returned.
Further reading: n/a