How NULL Works in Oracle Conditions

NULL means "no value": a commission that does not apply, a flight that has not arrived yet, a manager the chief executive does not have. It is not zero and not an empty text, and it does not behave like a value in conditions. Most surprising query results, rows that should be there but are not, come from forgetting that.

This guide explains three-valued logic, how to test for NULL, why comparisons skip NULL rows, the NOT IN trap, NULLs in aggregates, and Oracle's empty string rule.

Code for This Guide

The main examples are in the examples/basic-elements folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Three-Valued Logic

A condition in SQL is true, false, or unknown. Any comparison with NULL is unknown, because an unknown value might or might not equal the other side. AND, OR, and NOT then follow these rules:

• AND is false as soon as one side is false; otherwise an unknown side makes it unknown.
• OR is true as soon as one side is true; otherwise an unknown side makes it unknown.
• NOT unknown is still unknown.

In Oracle AI Database 26ai, conditions are BOOLEAN expressions, so you can see the logic directly. The example builds a small table of BOOLEAN pairs with a VALUES constructor, and SQLcl's SET NULL shows NULL as UNKNOWN.

Example:

set null UNKNOWN
select a, b, a and b as a_and_b, a or b as a_or_b, not a as not_a
from   (values (true, true), (true, false), (true, null), (false, null), (null, null))
       t (a, b);

Output:

A          B          A_AND_B    A_OR_B     NOT_A
__________ __________ __________ __________ __________
true       true       true       true       false
true       false      false      true       false
true       UNKNOWN    UNKNOWN    true       false
false      UNKNOWN    false      UNKNOWN    true
UNKNOWN    UNKNOWN    UNKNOWN    UNKNOWN    UNKNOWN

WHERE keeps a row only when its condition is true. Unknown counts as not true, so rows whose condition is unknown are left out, just like false ones.

Test for NULL with IS NULL

Because commission_pct = NULL is unknown for every row, it finds nothing, even for rows where the commission is NULL. Test with IS NULL and IS NOT NULL. A comparison such as commission_pct <> 0.05 also skips the NULL rows, since for them it is unknown as well.

Example:

select count(*) as all_employees,
       count(case when commission_pct = null then 1 end)      as equals_null,
       count(case when commission_pct is null then 1 end)     as is_null,
       count(case when commission_pct <> 0.05 then 1 end)     as not_5_percent,
       count(case when decode(commission_pct, 0.05, 1, 0) = 0 then 1 end) as decode_not_5
from   employees;

Output:

   ALL_EMPLOYEES    EQUALS_NULL    IS_NULL    NOT_5_PERCENT    DECODE_NOT_5
________________ ______________ __________ ________________ _______________
              55              0         48                4              52

Of 55 employees, 48 have no commission. "Not 5 percent" finds only 4, the commissioned employees with another rate, because the 48 NULL rows are unknown. If those rows should count as different, include them explicitly, as in commission_pct <> 0.05 or commission_pct is null, or compare with DECODE, which treats two NULLs as equal and finds 52.

The NOT IN Trap

x NOT IN (a, b, c) means x <> a AND x <> b AND x <> c. If the list contains a NULL, one of those comparisons is unknown, so the whole condition is never true and the query returns no rows. The chief executive has no manager, so the list of manager_id values contains a NULL.

Example:

-- employees who manage nobody: NOT IN finds none, because the list contains a NULL
select count(*) as with_not_in
from   employees
where  employee_id not in (select manager_id from employees);

select count(*) as with_not_exists
from   employees e
where  not exists (select null from employees x where x.manager_id = e.employee_id);

Output:

   WITH_NOT_IN
______________
             0

   WITH_NOT_EXISTS
__________________
                31

NOT IN finds no employee who manages nobody, while NOT EXISTS finds 31. Either use NOT EXISTS, or filter the NULLs out of the subquery:

Example:

-- filtering the NULLs out of the subquery makes NOT IN work
select count(*) as with_not_in_fixed
from   employees
where  employee_id not in (select manager_id from employees where manager_id is not null);

Output:

   WITH_NOT_IN_FIXED
____________________
                  31

IN does not have this problem: a NULL in the list simply never matches.

NULLs in Aggregates

Aggregate functions skip NULLs. COUNT(*) counts rows, but COUNT(column) counts only non-NULL values, and AVG divides by that smaller count.

Example:

-- aggregates skip NULLs: only 7 employees have a commission
select count(*) as employees, count(commission_pct) as with_commission,
       round(avg(commission_pct), 4) as avg_of_those,
       round(avg(nvl(commission_pct, 0)), 4) as avg_of_all
from   employees;

Output:

   EMPLOYEES    WITH_COMMISSION    AVG_OF_THOSE    AVG_OF_ALL
____________ __________________ _______________ _____________
          55                  7          0.0843        0.0107

The average of the seven commissions is 8.43%; treating missing commissions as zero with NVL gives 1.07% across all 55 employees. Decide which question you are asking before you choose.

The Empty String Is NULL

In Oracle, a text of length zero is NULL. '' IS NULL is true, LENGTH('') is NULL rather than 0, and concatenating a NULL with || simply adds nothing.

Example:

-- in Oracle, an empty string is NULL
select case when '' is null then 'empty string is NULL' else 'not NULL' end as test,
       length('') as len,
       'Dubai' || null || ' Airport' as concat_with_null
from   dual;

Output:

TEST                       LEN CONCAT_WITH_NULL
_______________________ ______ ___________________
empty string is NULL           Dubai Airport

This differs from most other databases, where '' is a value distinct from NULL. Code ported from them should test for NULL where it tested for ''.

Related Guides

• Oracle NVL Function: A Simple Guide
• Oracle LNNVL Function: A Simple Guide
• How to Write Semi-Joins with EXISTS in Oracle

Conclusion

Conditions in SQL are true, false, or unknown, and any comparison with NULL is unknown, so WHERE drops those rows. Test for NULL with IS NULL, include NULL rows explicitly when they should count, prefer NOT EXISTS to NOT IN, remember that aggregates skip NULLs, and treat the empty string as NULL in Oracle.