NULL in SQL and PL/SQL

NULL understanding in SQL and PL/SQL is fundamental knowledge required to be a good DBA and developer and to avoid surprises.

NULL – value that is unavailable, unassigned, unknown or inapplicable.

NULL is not the same as zero or a blank space.

Arithmetic operations and null

Always return NULL :). Easy and simple to remember.

select 
  12*null,
  12/null,
  12+null,
  12-null,
  null/null,
  null/3
from dual;

   12*NULL    12/NULL    12+NULL    12-NULL  NULL/NULL     NULL/3
---------- ---------- ---------- ---------- ---------- ----------

Boolean operations

Boolean operations

 Concatenation operator

NULL has got no impact on this operator

select 'hello tomasz'||null hello from dual;
HELLO      
------------
hello tomasz

 DISTINCT, COUNT

Let’s prepare first some dummy data

create table test_tbl
(id number);

insert into test_tbl values(null);
insert into test_tbl values(1);
insert into test_tbl values(1);
insert into test_tbl values(1);
insert into test_tbl values(2);
insert into test_tbl values(3);

commit;

select * from test_tbl;
        ID
----------

         1 
         1 
         1
         2 
         3

DISTINCT  is not ignoring nulls

select distinct id from test_tbl;
        ID
----------

         1 
         2 
         3

COUNT is ignoring nulls

 select count(id) from test_tbl;
 COUNT(ID)
----------
         5 

select count(distinct id) from test_tbl;
COUNT(DISTINCTID)
-----------------
                3

Comparison operators

To recognize NULL you need to use IS NULL or IS NOT NULL operators. In other case results can be surprising.

Wrong coding style in PL/SQL. In both cases somebody could expect TRUE.

set serveroutput on
declare
  v_id   number;
begin
  v_id := null;

  if v_id = null then
    dbms_output.put_line('TRUE');
  else
    dbms_output.put_line('FALSE');
  end if;

  v_id := 10;

  if v_id != null then
    dbms_output.put_line('TRUE');
  else
    dbms_output.put_line('FALSE');
  end if;
end;
/

anonymous block completed
FALSE
FALSE

The same code properly written

set serveroutput on
declare
  v_id   number;
begin
  v_id := null;

  if v_id is null then
    dbms_output.put_line('TRUE');
  else
    dbms_output.put_line('FALSE');
  end if;

  v_id := 10;

  if v_id is not null then
    dbms_output.put_line('TRUE');
  else
    dbms_output.put_line('FALSE');
  end if;
end;
/

anonymous block completed
TRUE
TRUE

the same is for SQL code

bad coding style

select * from test_tbl where id = null;

no rows selected

select * from test_tbl where id != null;

no rows selected

select * from test_tbl where id > null;

no rows selected

select * from test_tbl where id < null;

no rows selected

select * from test_tbl where id >= null;

no rows selected

select * from test_tbl where id <= null;

no rows selected

select * from test_tbl where id like null;

no rows selected

proper coding style

select * from test_tbl where id is null;

        ID
----------

select * from test_tbl where id is not null;

        ID
----------
         1 
         1 
         1 
         2 
         3

Comparison for IN, NOT IN, BETWEEN, EXISTS, NOT EXISTS

Now more advanced examples that can make your heart beating much faster.

IN operator

Somebody can be surprised that null are excluded

select * from test_tbl where id in(1, null);

        ID
----------
         1 
         1 
         1

Above statement is in fact following

select * from test_tbl 
 where id = 1
union all
select * from test_tbl 
  where id = null  <- here is problem it returns FALSE

to include null it should be written like this

select * from test_tbl where id in(1) or id is null;

        ID
----------

         1 
         1 
         1

Usually the problem appears when a developer is using sub-queries.

Where are my NULLS !!! 🙂

select * from test_tbl 
where id in (select * from test_tbl);

        ID
----------
         1 
         1 
         1 
         2 
         3

NOT IN operator

Somebody can be surprised that no rows are selected

select * from test_tbl where id not in(1, null);

no rows selected

Above statement is in fact following

select * from test_tbl   
where id !=1     
  and id != null;  <- here is problem it returns always FALSE

to make it work it should be written like this

select * from test_tbl 
  where id not in (1) 
    and id is not null;

        ID
----------
         2 
         3

Usually the problem appears when a developer is using sub-queries.

One NULL from sub-query is blocking all !!! It’s very common problem.

select * from test_tbl 
where id not in (select 1 from dual 
                 union all 
                 select null from dual);

EXISTS operator

Some developer could expect all data but NULLS are gone

select * from test_tbl a
where exists(select 1
             from test_tbl b
             where a.id=b.id);

        ID
----------
         1 
         1 
         1 
         2 
         3

all because NULL=NULL returns FALSE

NOT EXISTS operator

Some developer could expect no rows but rows are returned for NULLS

select * from test_tbl a
where not exists(select 1
             from test_tbl b
             where a.id=b.id);

        ID
----------

because NOT NULL=NULL   returns TRUE

BETWEEN operator

Rather rare case however instead of null you could have function which returned NULL value. So instead of range of rows you got nothing.

select * from test_tbl
where id between null and 2;

no rows selected

select * from test_tbl
where id between 2 and null;

no rows selected

ORDER BY

As default NULLs data are returned as last

select * from test_tbl
order by id;

        ID
----------
         1 
         1 
         1 
         2 
         3 

 6 rows selected

you can change it

select * from test_tbl
order by id nulls first;

        ID
----------

         1 
         1 
         1 
         2 
         3 

 6 rows selected

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *