Inequality and NULL

A response recent forums question about handling an inequality condition also reminded me, once again, about NULL handling in Oracle.

You can have :

WHERE column = 100 

in a query.

In another query, you could have

WHERE column != 100 

However, if the column does contain a NULL in any one (or more rows), the union of the two queries is not the entire table. That is, rows with NULLs are excluded.
You have to specifically handle NULLs with either

IS NULL 

or

IS NOT NULL 

depending on whether you want to explicitly include or exclude NULLs.


Here's a quick demo of the behaviour of NULLs :

SQL> create table TEST_ROWS as
2  select rownum as COLUMN_1,
3  'Row Number :  ' || to_char(rownum) as COLUMN_2
4  from dual connect by level < 11 ;

Table created.

SQL> insert into TEST_ROWS values (NULL,'Row NULL');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select COLUMN_1, COLUMN_2 from TEST_ROWS order by 1 ;

COLUMN_1 COLUMN_2
---------- ------------------------------------------------------
  1 Row Number :  1
  2 Row Number :  2
  3 Row Number :  3
  4 Row Number :  4
  5 Row Number :  5
  6 Row Number :  6
  7 Row Number :  7
  8 Row Number :  8
  9 Row Number :  9
 10 Row Number :  10
    Row NULL

11 rows selected.

SQL>
SQL> select * from TEST_ROWS where COLUMN_1 != 6 order by 1;

COLUMN_1 COLUMN_2
---------- ------------------------------------------------------
  1 Row Number :  1
  2 Row Number :  2
  3 Row Number :  3
  4 Row Number :  4
  5 Row Number :  5
  7 Row Number :  7
  8 Row Number :  8
  9 Row Number :  9
 10 Row Number :  10

9 rows selected.

SQL>  

Notice how the query for != 6 does *not* show the row with a NULL.
Oracle does not say that a NULL is != 6. A NULL is not comparable with 6 !
The query has to be actually :

SQL> select * from TEST_ROWS
2  where (COLUMN_1 != 6   OR  COLUMN_1 IS NULL)
3  order by 1;

COLUMN_1 COLUMN_2
---------- ------------------------------------------------------
  1 Row Number :  1
  2 Row Number :  2
  3 Row Number :  3
  4 Row Number :  4
  5 Row Number :  5
  7 Row Number :  7
  8 Row Number :  8
  9 Row Number :  9
 10 Row Number :  10
    Row NULL

10 rows selected.

SQL> 

It is now that the row with a NULL appears !

That is why such a count :

SQL> select count(*) from TEST_ROWS;

COUNT(*)
----------
 11

SQL>
SQL> select count(*) from
2  (select * from TEST_ROWS where COLUMN_1 = 6
3  union
4   select * from TEST_ROWS where COLUMN_1 != 6)
5  /

COUNT(*)
----------
 10

SQL>
SQL> select count(*) from
2  (select * from TEST_ROWS where COLUMN_1 = 6
3   union all
4   select * from TEST_ROWS where COLUMN_1 != 6)
5  /

COUNT(*)
----------
 10

SQL>  

would fail because the union of the two result sets (equal to 6 and not-equal to 6) does not comprise of the whole set !
.
.
.