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
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