Dealing with NULL values in Oracle

NULL is a special beast in Oracle — it's not zero, not an empty string, and comparing anything to NULL yields NULL (not TRUE or FALSE). Oracle provides several functions to handle NULLs gracefully.

Recipe #1 - NVL — Replace NULL with a default

NVL returns the second argument if the first is NULL:

SELECT ename,
       NVL(comm, 0) AS commission,
       sal + NVL(comm, 0) AS total_compensation
FROM emp;

Recipe #2 - NVL2 — Different values for NULL and non-NULL

NVL2 returns one value if the expression is NOT NULL, and another if it IS NULL:

SELECT ename,
       NVL2(comm, 'Has commission', 'No commission') AS comm_status,
       NVL2(comm, sal + comm, sal) AS total_pay
FROM emp;

Recipe #3 - COALESCE — First non-NULL from a list

COALESCE is the ANSI standard version and accepts multiple arguments. It returns the first non-NULL value:

SELECT ename,
       COALESCE(comm, bonus, 0) AS extra_pay
FROM emp;

Key difference from NVL: COALESCE uses short-circuit evaluation — it stops evaluating once it finds a non-NULL value. NVL always evaluates both arguments.

Recipe #4 - DECODE — Compact conditional logic

DECODE is Oracle's compact IF-THEN-ELSE for use in SQL:

SELECT ename, deptno,
       DECODE(deptno,
              10, 'Accounting',
              20, 'Research',
              30, 'Sales',
              'Other') AS department_name
FROM emp;

Recipe #5 - CASE — The ANSI standard alternative to DECODE

CASE is more readable and supports ranges and complex conditions:

SELECT ename, sal,
       CASE
         WHEN sal >= 4000 THEN 'Executive'
         WHEN sal >= 2500 THEN 'Senior'
         WHEN sal >= 1000 THEN 'Mid-level'
         ELSE 'Junior'
       END AS salary_grade
FROM emp;

Recipe #6 - NULL pitfalls to remember

Some common traps when working with NULLs:

-- NULL is never equal to NULL
SELECT * FROM emp WHERE comm = NULL;     -- Returns NO rows!
SELECT * FROM emp WHERE comm IS NULL;    -- Correct way

-- NULL in calculations makes the result NULL
SELECT 100 + NULL FROM DUAL;              -- Returns NULL
SELECT 100 + NVL(NULL, 0) FROM DUAL;     -- Returns 100

-- NULL in string concatenation
SELECT 'Hello' || NULL || 'World' FROM DUAL;  -- Returns 'HelloWorld'
-- (Oracle treats NULL as empty string in concatenation)

-- COUNT ignores NULLs
SELECT COUNT(comm), COUNT(*) FROM emp;
-- COUNT(comm) only counts non-NULL values
-- COUNT(*) counts all rows