Oracle SQL - Null Value in Not Equal Condition

  1. HOME
  2. Oracle SQL
  3. Select
  4. Null Values

Introduction

The following query produces "no rows selected"? There are registrations with evaluation values 4 and 5. 

select * from registrations
where evaluation not in (1,2,3,NULL);

The following WHERE clause: 

where evaluation not in (1,2,3,NULL)

is logically equivalent with the following "iterated AND" condition: 

where evaluation <> 1
AND   evaluation <> 2
AND   evaluation <> 3
AND   evaluation <> NULL

If the EVALUATION value is NULL, the end result is UNKNOWN.

So you have the following expression: 

(TRUE) and (TRUE) and (TRUE) and (UNKNOWN)

This is logically equivalent with UNKNOWN, so the complete WHERE clause returns UNKNOWN.

To fix it, use the following query 

where evaluation not in (1,2,3)
  and evaluation is not null

then the new result would return any rows with an evaluation value of 4 or 5, but no other rows.

Related Topic