SQL Null Values

In SQL, a NULL value indicates missing, unknown, or unassigned data. It is completely different from a blank space (text strings) or a value of zero (numbers)—it represents the complete absence of a value.

Because NULL is not a definitive value, you cannot use standard comparison operators like = or != to find it. Instead, SQL provides two dedicated operators: IS NULL and IS NOT NULL.

The IS NULL Operator

Use this operator to find rows where data is completely missing or empty.

SELECT column1, column2
FROM table_name
WHERE column_name IS NULL;

Real-World Example:

Imagine an onboarding system where users can sign up but skip adding their phone number. To find all users who haven't completed their profiles:

SELECT username, email FROM users
WHERE phone_number IS NULL;

The IS NOT NULL Operator

Use this operator to filter out missing records and retrieve only rows that contain valid data.

SELECT column1, column2
FROM table_name
WHERE column_name IS NOT NULL;

Real-World Example:

To extract a list of addresses to ship out items, you need to make sure the street address field isn't empty:

SELECT customer_name, shipping_address FROM orders
WHERE shipping_address IS NOT NULL;

The Golden Rule: Never Use = NULL

A very common mistake is trying to write WHERE column = NULL. In database logic, comparing anything to an unknown (NULL) always results in an unknown outcome. Therefore, = NULL will return zero rows and fail silently.

-- ❌ THIS WILL NOT WORK (Returns 0 results)
SELECT * FROM employees WHERE manager_id = NULL;

--  THIS IS THE CORRECT SYNTAX
SELECT * FROM employees WHERE manager_id IS NULL;

Handling NULLs in Calculations (IFNULL / COALESCE)

If you try to perform arithmetic on a NULL value (e.g., salary + bonus), and the bonus is NULL, the entire result becomes NULL. To fix this, databases offer functions to replace NULL with a fallback value on the fly:

MySQL: IFNULL(bonus, 0)
SQL Server: ISNULL(bonus, 0)
PostgreSQL / Oracle / Standard ANSI: COALESCE(bonus, 0)

-- Replaces any NULL bonus with 0 so the math calculation doesn't break
SELECT first_name, salary + COALESCE(bonus, 0) AS total_compensation
FROM employees;