How to Handle NULL in SQL

Use NULL checks when a column may have missing, unknown, or intentionally empty values. SQL treats NULL differently from normal values, so it needs special syntax for filtering and fallback logic.

What you’ll build or solve

You’ll learn how to handle NULL in SQL with IS NULL, IS NOT NULL, and fallback helpers like COALESCE(). You’ll also know why = does not work with NULL.

When this approach works best

This approach is the right choice when your query must include or exclude missing values safely.

Common real-world scenarios include:

• Missing phone numbers
• Unassigned managers
• Optional shipping dates
• Empty profile images
• Fallback display values

This is a bad idea when the column should never allow missing values. In that case, enforce NOT NULL in the schema.

Prerequisites

You only need:

• A SQL table with nullable columns
• Basic SELECT and WHERE knowledge

Step-by-step instructions

Step 1: Use IS NULL and IS NOT NULL

To find missing values, use IS NULL.

SELECT *
FROM users
WHERE phone_number IS NULL;

To find rows where a value exists, use IS NOT NULL.

SELECT *
FROM users
WHERE avatar_url IS NOT NULL;

For display fallbacks, use COALESCE().

SELECT COALESCE(phone_number, 'Not provided') AS phone
FROM users;

This replaces NULL with a readable fallback.

What to look for:

• Use IS NULL for missing values
• Use IS NOT NULL for existing values
• = does not work with NULL
• COALESCE() provides fallback output
• Great for optional fields

Examples you can copy

Missing manager

SELECT *
FROM employees
WHERE manager_id IS NULL;

Assigned delivery dates

SELECT *
FROM orders
WHERE shipped_at IS NOT NULL;

Fallback nickname

SELECT COALESCE(nickname, first_name) AS display_name
FROM users;

Common mistakes and how to fix them

Mistake 1: Using = with NULL

What the reader might do:

WHERE phone_number = NULL

Why it breaks: SQL uses special NULL comparison rules.

Corrected approach:

WHERE phone_number IS NULL

Mistake 2: Forgetting COALESCE() in display queries

What the reader might do:

SELECT nickname
FROM users;

Why it breaks: missing nicknames may show blank values in reports.

Corrected approach:

SELECT COALESCE(nickname, 'Guest')
FROM users;

Mistake 3: Treating empty strings as NULL

What the reader might do:

WHERE nickname IS NULL

Why it breaks: empty strings '' are not always the same as NULL.

Corrected approach:

Handle both cases when needed.

WHERE nickname IS NULL
OR nickname = ''

Troubleshooting

If NULL filters never match, switch from = to IS NULL.

If blank strings still appear, also check for ''.

If reports show empty cells, add COALESCE().

If the column should always exist, enforce NOT NULL.

Quick recap

• Use IS NULL for missing values
• Use IS NOT NULL for existing values
• Never use = with NULL
• Use COALESCE() for fallbacks
• Empty strings may need separate checks