Lesson 11 Intermediate Level
11 of 30 lessons

NULL Handling (IS NULL, NVL)

Learn how to work with NULL values using IS NULL, IS NOT NULL, and NVL functions. Understanding NULL handling is crucial for writing robust SQL queries.

What is NULL?

In SQL, NULL represents a missing or unknown value. It is not the same as zero or an empty string. NULLs must be handled explicitly in queries to avoid unexpected results.

Using IS NULL and IS NOT NULL

SELECT employee_id, commission_pct
FROM employees
WHERE commission_pct IS NULL;

SELECT employee_id, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

Use IS NULL or IS NOT NULL to check for the presence or absence of a NULL value.

Using NVL Function

The NVL function replaces NULL values with a specified replacement. 

SELECT first_name, NVL(commission_pct, 0)
FROM employees;

This replaces all NULL commission values with 0.

Practice Task