Oracle SQL NULL Values

Oracle SQL NULL Values: Complete Guide

NULL is one of the most misunderstood and frequently tested concepts in Oracle SQL. It appears in almost every topic — WHERE clauses, aggregates, joins, ORDER BY, and single-row functions. Understanding exactly how Oracle treats NULL is essential for writing correct queries.

Learning Objectives
By the end of this guide you will understand three-valued logic, why rows disappear silently, the correct way to test for NULL, how NULL behaves in arithmetic, string operations, and aggregates, and how to use NVL, COALESCE, and NULLIF effectively.

1. What NULL Actually Is

NULL means the value is unknown or missing. It is not zero. It is not an empty string. It is simply absent.

When you insert a row without providing a value for a column, or when you explicitly insert NULL, the column holds NULL. From a query perspective, these two cases are identical.

-- Both produce a NULL in commission_pct
INSERT INTO employees (employee_id, last_name, salary, commission_pct)
VALUES (300, 'Ferris', 5000, NULL);

INSERT INTO employees (employee_id, last_name, salary)
VALUES (301, 'Grant', 5000);

2. Three-Valued Logic

Oracle uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL produces UNKNOWN.

The WHERE clause only keeps rows where the condition is TRUE. Both FALSE and UNKNOWN rows are excluded — silently.

Expression	Result	Row returned by WHERE?
5 = 5	TRUE	Yes
5 = 6	FALSE	No
NULL = NULL	UNKNOWN	No
NULL = 5	UNKNOWN	No
NULL != 5	UNKNOWN	No

Common trap: WHERE commission_pct != 0.25 does not include employees with no commission (NULL). The comparison produces UNKNOWN, so those rows are excluded.

3. The Only Safe Tests for NULL

Use IS NULL and IS NOT NULL. These are the only operators that return TRUE or FALSE when testing NULL.

SELECT last_name 
FROM   employees 
WHERE  commission_pct IS NULL;

SELECT last_name 
FROM   employees 
WHERE  commission_pct IS NOT NULL;

Never use = NULL or != NULL — these are valid syntax but always produce UNKNOWN, so no rows are ever returned.

4. NULL in Arithmetic and String Expressions

In arithmetic, NULL spreads: any expression containing NULL returns NULL.

salary + commission_pct          -- Returns NULL if commission_pct is NULL
salary * 12 + NVL(commission_pct, 0) * salary * 12  -- Safe alternative

String concatenation is different in Oracle: NULL is treated as an empty string.

'Hello' || NULL || ' World'   -- Returns: 'Hello World'

5. NULL in Aggregate Functions

Aggregate functions ignore NULL rows — except COUNT(*).

Function	NULL rows	Empty group result
COUNT(*)	Counted	0
COUNT(col)	Skipped	0
SUM, AVG, MAX, MIN	Skipped	NULL

6. Useful NULL Functions

NVL(commission_pct, 0)                    -- Replace NULL with 0
NVL2(commission_pct, 'Yes', 'No')         -- Different value for NULL vs non-NULL
COALESCE(commission_pct, bonus, 0)        -- First non-NULL value
NULLIF(hours_worked, 0)                   -- Return NULL if value is 0

7. Interactive Practice Quiz

Q1. EMPLOYEES has 107 rows. COMMISSION_PCT is NULL for 35 rows. How many rows does this return?
SELECT last_name FROM employees WHERE commission_pct != 0.25;

A. 107 rows B. 72 rows C. Fewer than 72 rows D. The query raises an error

Q2. Which expression correctly returns 'None' for employees with no manager (manager_id is NULL)?

A. WHERE manager_id = NULL B. NVL(TO_CHAR(manager_id), 'None') C. COALESCE(TO_CHAR(manager_id), 'None') D. Both B and C

Q3. What does AVG(commission_pct) calculate when 35 of 107 employees have NULL commission_pct?

A. Total commission divided by 107 B. Total commission divided by 72 C. NULL because some values are NULL D. Zero (NULLs treated as 0)

Conclusion

NULL is not a value — it is the absence of a value. Understanding three-valued logic, the correct way to test for NULL, and how NULL behaves in different contexts will prevent many common mistakes in your queries.

Practice using IS NULL / IS NOT NULL, NVL, COALESCE, and careful aggregate functions — these patterns appear in almost every real-world Oracle SQL task.