Handling NULL Values

Work with NULL values using IS NULL and IS NOT NULL.

12 min read
Beginner

Introduction to NULL Values

In SQL, NULL is special - it represents the absence of a value. It's not zero, not an empty string, not false. It means "unknown" or "not applicable."

NULL values appear everywhere in real databases:

  • An employee's bonus might be NULL (not determined yet)
  • A customer's phone number might be NULL (not provided)
  • An order's delivery_date might be NULL (not shipped yet)

Understanding how NULL behaves is crucial because it acts differently than you might expect!

A special marker in SQL that indicates missing or unknown data. NULL is not equal to anything, not even another NULL. It represents the absence of a value.

Common misconceptions:

  • NULL is NOT zero (0)
  • NULL is NOT an empty string ('')
  • NULL is NOT false
  • NULL IS NOT equal to NULL!

Let's work with data that contains NULL values:

employees
id
name
department
salary
bonus
phone
1AliceEngineering950005000555-0101
2BobMarketing75000NULL555-0102
3CharlieEngineering850008000NULL
4DianaSales70000NULLNULL

NULL in Comparisons - The Unexpected Behavior

Here's the biggest surprise about NULL: NULL is not equal to anything, including NULL itself!

sql
-- Try to find employees with no bonus using = NULL (WRONG!)
SELECT name, bonus
FROM employees
WHERE bonus = NULL;

This returned zero rows even though Bob and Diana have NULL bonuses! Why?

Because bonus = NULL always evaluates to NULL (unknown), not TRUE or FALSE. And WHERE filters only keep rows where the condition is TRUE.

The correct way - use IS NULL:

sql
-- Find employees with no bonus (CORRECT)
SELECT name, bonus
FROM employees
WHERE bonus IS NULL;

IS NULL is the special operator for checking if a value is NULL.

To find non-NULL values, use IS NOT NULL:

sql
-- Find employees who HAVE a bonus
SELECT name, bonus
FROM employees
WHERE bonus IS NOT NULL;

Critical Rule:

  • Use IS NULL to check for NULL
  • Use IS NOT NULL to check for non-NULL
  • NEVER use = NULL or != NULL - they don't work!

NULL in Arithmetic Operations

Any arithmetic operation with NULL results in NULL:

sql
-- Calculate total compensation
SELECT
  name,
  salary,
  bonus,
  salary + bonus AS total_compensation
FROM employees;

Bob and Diana's total_compensation is NULL because 75000 + NULL = NULL and 70000 + NULL = NULL.

NULL propagates through calculations:

  • 10 + NULL = NULL
  • 100 * NULL = NULL
  • NULL / 5 = NULL
  • NULL - NULL = NULL

COALESCE - Providing Default Values

COALESCE() returns the first non-NULL value from a list:

sql
-- Treat NULL bonus as 0
SELECT
  name,
  salary,
  bonus,
  COALESCE(bonus, 0) AS bonus_or_zero,
  salary + COALESCE(bonus, 0) AS total_compensation
FROM employees;

Perfect! Now everyone has a valid total_compensation.

COALESCE with multiple fallbacks:

sql
-- Try multiple columns, use first non-NULL
SELECT
  name,
  COALESCE(phone, email, 'No contact info') AS contact
FROM employees;

COALESCE checks phone first, then email, then uses the default string.

NULLIF - Creating NULL Values

NULLIF() returns NULL if two values are equal, otherwise returns the first value:

sql
-- Convert empty strings to NULL
SELECT
  name,
  NULLIF(phone, '') AS phone_cleaned
FROM employees;

Useful for:

  • Converting sentinel values (like -1 or '') to NULL
  • Avoiding division by zero
sql
-- Avoid division by zero
SELECT
  total_sales,
  order_count,
  total_sales / NULLIF(order_count, 0) AS average_order
FROM sales_summary;

If order_count is 0, NULLIF returns NULL, and the division returns NULL instead of causing an error.

NULL in Aggregate Functions

Most aggregate functions ignore NULL values:

sql
-- Aggregates ignore NULL
SELECT
  COUNT(*) AS total_employees,
  COUNT(bonus) AS employees_with_bonus,
  SUM(bonus) AS total_bonuses,
  AVG(bonus) AS avg_bonus
FROM employees;

Notice:

  • COUNT(*) counts all 4 employees
  • COUNT(bonus) counts only 2 (Alice and Charlie have bonuses)
  • SUM(bonus) = 5000 + 8000 = 13000 (NULLs ignored)
  • AVG(bonus) = 13000 / 2 = 6500 (divides by non-NULL count)

Important: AVG ignores NULLs when calculating the average. If you want to treat NULL as 0:

sql
-- Include NULL bonuses as 0 in average
SELECT
  AVG(COALESCE(bonus, 0)) AS avg_bonus_including_zero
FROM employees;

Now it's (5000 + 0 + 8000 + 0) / 4 = 3250, which is different!

NULL in Logical Operations (AND, OR, NOT)

NULL in logical operations introduces three-valued logic: TRUE, FALSE, and NULL (unknown).

AND Truth Table with NULL
Condition 1
Condition 2
Result
TRUETRUETRUE
TRUEFALSEFALSE
TRUENULLNULL
FALSENULLFALSE
NULLNULLNULL
OR Truth Table with NULL
Condition 1
Condition 2
Result
TRUENULLTRUE
FALSENULLNULL
NULLNULLNULL
sql
-- Unexpected behavior with NULL in WHERE
SELECT name, bonus, phone
FROM employees
WHERE bonus > 5000 OR phone IS NULL;

Bob (bonus=NULL, phone='555-0102') is excluded because:

  • bonus > 5000 evaluates to NULL (NULL comparison)
  • phone IS NULL evaluates to FALSE (he has a phone)
  • NULL OR FALSE = NULL (not TRUE, so excluded)

NULL in ORDER BY

How does ORDER BY handle NULL values?

sql
-- Sort by bonus (ascending)
SELECT name, bonus
FROM employees
ORDER BY bonus ASC;

In most databases (MySQL, PostgreSQL):

  • NULL appears first with ASC (ascending)
  • NULL appears last with DESC (descending)

Control NULL ordering:

sql
-- Put NULLs last even with ASC (PostgreSQL syntax)
SELECT name, bonus
FROM employees
ORDER BY bonus ASC NULLS LAST;

Database differences:

  • MySQL/PostgreSQL: NULL first with ASC, last with DESC
  • SQL Server: NULL first with ASC, last with DESC
  • Oracle: NULL last with ASC, first with DESC

PostgreSQL and Oracle support NULLS FIRST and NULLS LAST keywords.

NULL in DISTINCT and GROUP BY

DISTINCT treats all NULL values as equal (one NULL appears in results):

sql
-- Get distinct bonus values
SELECT DISTINCT bonus
FROM employees
ORDER BY bonus;

Only one NULL appears, even though Bob and Diana both have NULL bonuses.

GROUP BY also treats NULLs as equal:

sql
-- Count employees by bonus
SELECT bonus, COUNT(*) AS employee_count
FROM employees
GROUP BY bonus;

Bob and Diana are grouped together under NULL.

Common Mistakes to Avoid

Don't make these mistakes:

  1. Using = NULL or != NULL: ❌

    • Wrong: WHERE bonus = NULL
    • Right: WHERE bonus IS NULL

  2. Forgetting NULL in arithmetic: ❌

    • salary + bonus returns NULL if bonus is NULL
    • Use: salary + COALESCE(bonus, 0)

  3. Using COUNT(*) when you mean COUNT(column): ❌

    • COUNT(*) counts all rows (including NULL)
    • COUNT(column) counts non-NULL values

  4. Assuming NULL = 0 or NULL = '': ❌

    • NULL is distinct from 0 and empty string
    • Must handle explicitly

  5. Not handling NULL in AVG: ❌

    • AVG ignores NULL (doesn't count them in average)
    • Decide if you want to treat NULL as 0

  6. Comparing two NULLs with =: ❌

    • NULL = NULL is NULL (unknown), not TRUE
    • Use IS NULL for comparisons

  7. Unexpected NULL propagation: ❌

    • WHERE price > 100 OR price <= 100 doesn't catch NULL prices
    • Add: OR price IS NULL if needed

  8. Not using COALESCE for user-facing output: ❌

    • Displaying NULL to users is confusing
    • Use COALESCE to show 'N/A', '0', or meaningful defaults

Practical Examples

Example 1: Customer contact report

sql
-- Show customers with contact info, flag missing data
SELECT
  name,
  COALESCE(email, 'No email') AS email,
  COALESCE(phone, 'No phone') AS phone,
  CASE
    WHEN email IS NULL AND phone IS NULL THEN 'No contact info'
    WHEN email IS NULL THEN 'Missing email'
    WHEN phone IS NULL THEN 'Missing phone'
    ELSE 'Complete'
  END AS status
FROM customers;

Example 2: Product pricing with discounts

sql
-- Calculate final price (NULL discount means no discount)
SELECT
  product_name,
  price,
  discount,
  price - COALESCE(discount, 0) AS final_price
FROM products;

Example 3: Employee bonus report

sql
-- Report showing bonus status
SELECT
  name,
  department,
  salary,
  COALESCE(bonus, 0) AS bonus,
  salary + COALESCE(bonus, 0) AS total_comp,
  CASE
    WHEN bonus IS NULL THEN 'No bonus'
    WHEN bonus > 5000 THEN 'High bonus'
    ELSE 'Standard bonus'
  END AS bonus_category
FROM employees
ORDER BY total_comp DESC;

Example 4: Finding incomplete records

sql
-- Find employees missing critical information
SELECT
  name,
  department,
  CASE
    WHEN phone IS NULL THEN 'Missing phone'
    ELSE 'OK'
  END AS phone_status,
  CASE
    WHEN department IS NULL THEN 'Missing department'
    ELSE 'OK'
  END AS dept_status
FROM employees
WHERE phone IS NULL
   OR department IS NULL;

Key Takeaways

What you learned:

✅ NULL represents missing or unknown data ✅ NULL is NOT equal to 0, '', or false ✅ Use IS NULL and IS NOT NULL for comparisons (never = NULL) ✅ COALESCE() provides default values for NULL ✅ NULLIF() converts specific values to NULL ✅ Arithmetic with NULL returns NULL (NULL propagation) ✅ Aggregate functions ignore NULL (except COUNT(*)) ✅ NULL in WHERE creates three-valued logic (TRUE/FALSE/NULL) ✅ ORDER BY, DISTINCT, and GROUP BY have special NULL handling ✅ Always consider NULL when writing queries!

Coming up next: We'll learn about string functions to manipulate and transform text data!

Practice Exercise: Try these queries:

  1. Find employees with NULL bonuses
  2. Calculate total compensation treating NULL bonus as 0
  3. Count employees who have vs don't have phone numbers
  4. Show all employees, with 'No phone' for NULL phone values
  5. Find average bonus including NULLs as 0
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.