MySQL NULL Values

Introduction

In MySQL and other database systems, NULL represents a missing or unknown value. Understanding how to work with NULL values is essential for effective database design and querying. Unlike zero (0) or an empty string (''), NULL indicates the absence of any value, which requires special handling in your SQL queries.

This guide will explore what NULL values are, how to test for them, and various techniques to handle them properly in your MySQL database operations.

What is a NULL Value?

A NULL value in MySQL indicates:

• A missing value
• An unknown value
• An inapplicable value

It's important to understand that NULL is not:

• Zero (0)
• An empty string ('')
• False
• A space character (' ')

NULL represents the absence of data rather than a specific value.

Creating Tables with NULL Values

When creating a table in MySQL, you can specify whether a column allows NULL values or not using the NULL or NOT NULL constraint.

sql

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL,
    birth_date DATE NULL
);

In this example:

• first_name, last_name, and email columns cannot contain NULL values
• phone and birth_date columns can contain NULL values

Testing for NULL Values

A common mistake beginners make is trying to use standard comparison operators (=, !=) with NULL. Since NULL represents an unknown value, these operators don't work as expected.

Incorrect Way to Test for NULL

sql

-- This will NOT find rows where email is NULL
SELECT * FROM customers WHERE email = NULL;

-- This will NOT find rows where email is not NULL
SELECT * FROM customers WHERE email != NULL;

Correct Way to Test for NULL

To test for NULL values, use the IS NULL and IS NOT NULL operators:

sql

-- Find customers with no phone number
SELECT * FROM customers WHERE phone IS NULL;

-- Find customers who have provided a phone number
SELECT * FROM customers WHERE phone IS NOT NULL;

Working with NULL Values in Queries

Inserting NULL Values

You can insert NULL values explicitly or by omitting columns in your INSERT statement:

sql

-- Explicitly inserting NULL
INSERT INTO customers (customer_id, first_name, last_name, email, phone, birth_date)
VALUES (1, 'John', 'Doe', '[email protected]', NULL, '1990-01-15');

-- Omitting optional columns (will set them to NULL if allowed)
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (2, 'Jane', 'Smith', '[email protected]');

NULL in WHERE Clauses

When using WHERE clauses with multiple conditions, be aware of how NULL values affect logical operators:

sql

-- Customers with either NULL phone or NULL birth_date
SELECT * FROM customers WHERE phone IS NULL OR birth_date IS NULL;

-- Customers with both NULL phone and NULL birth_date
SELECT * FROM customers WHERE phone IS NULL AND birth_date IS NULL;

NULL in JOIN Operations

NULL values can affect JOIN operations, especially with equality conditions:

sql

-- This JOIN won't match rows where either orders.customer_id or customers.customer_id is NULL
SELECT * FROM orders 
JOIN customers ON orders.customer_id = customers.customer_id;

Handling NULL Values

MySQL provides several functions to handle NULL values in your queries:

IFNULL() Function

The IFNULL() function returns a specified value if the expression is NULL, otherwise it returns the expression itself.

sql

-- Replace NULL phone values with "Not provided"
SELECT 
    first_name, 
    last_name,
    IFNULL(phone, 'Not provided') AS contact_number
FROM customers;

Example output:

first_name	last_name	contact_number
John	Doe	555-1234
Jane	Smith	Not provided
Michael	Johnson	555-5678

COALESCE() Function

The COALESCE() function returns the first non-NULL value in a list. This is more flexible than IFNULL() as it can check multiple values:

sql

-- Try to use phone; if NULL, try to use email; if both NULL, use "No contact info"
SELECT 
    first_name, 
    last_name,
    COALESCE(phone, email, 'No contact info') AS contact_info
FROM customers;

Example output:

first_name	last_name	contact_info
John	Doe	555-1234
Jane	Smith	[email protected]
Tom	Wilson	No contact info

NULLIF() Function

The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first expression:

sql

-- Convert empty strings to NULL
SELECT NULLIF(column_name, '') FROM table_name;

-- Example: Convert '0' phone numbers to NULL
SELECT 
    first_name,
    last_name,
    NULLIF(phone, '0') AS phone
FROM customers;

NULL Values with Aggregate Functions

NULL values are handled specially with aggregate functions:

• COUNT(*) counts all rows including those with NULL values
• COUNT(column) counts only rows where the column has non-NULL values
• Other aggregate functions (SUM, AVG, MIN, MAX) ignore NULL values

sql

-- Counts all customers
SELECT COUNT(*) FROM customers;

-- Counts only customers with a phone number
SELECT COUNT(phone) FROM customers;

-- Average age, ignoring NULL birth dates
SELECT AVG(TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) AS avg_age 
FROM customers;

Practical Example: Customer Contact Report

Let's create a more comprehensive example. Imagine you need to generate a contact report for your customers:

sql

SELECT 
    customer_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    email,
    CASE 
        WHEN phone IS NULL THEN 'No phone provided'
        ELSE phone
    END AS phone_number,
    CASE 
        WHEN birth_date IS NULL THEN 'Unknown'
        WHEN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18 THEN 'Adult'
        ELSE 'Minor'
    END AS age_category,
    COALESCE(
        phone, 
        CONCAT('Email only: ', email), 
        'No contact method'
    ) AS preferred_contact
FROM customers;

This query:

1. Creates a full name from first and last name
2. Handles NULL phone numbers with a descriptive message
3. Categorizes customers by age, accounting for NULL birth dates
4. Creates a preferred contact method, cascading through options

Real-world Application: Filtering Survey Results

Consider a survey application where participants can leave questions unanswered:

sql

CREATE TABLE survey_responses (
    response_id INT PRIMARY KEY,
    participant_id INT NOT NULL,
    q1_rating INT NULL,  -- Rating from 1-5, NULL if skipped
    q2_rating INT NULL,
    q3_text VARCHAR(500) NULL,  -- Text response, NULL if skipped
    completed BOOLEAN NOT NULL
);

To analyze the survey results effectively:

sql

-- Calculate average ratings, handling NULL values
SELECT 
    AVG(q1_rating) AS q1_avg,
    AVG(q2_rating) AS q2_avg,
    COUNT(q3_text) AS text_responses,
    COUNT(*) AS total_participants,
    COUNT(q3_text) / COUNT(*) * 100 AS text_response_rate
FROM survey_responses
WHERE completed = TRUE;

-- Find questions most commonly skipped
SELECT
    COUNT(*) - COUNT(q1_rating) AS q1_skipped,
    COUNT(*) - COUNT(q2_rating) AS q2_skipped,
    COUNT(*) - COUNT(q3_text) AS q3_skipped
FROM survey_responses
WHERE completed = TRUE;

-- Group participants by completion pattern
SELECT
    CASE WHEN q1_rating IS NULL THEN 'Skipped' ELSE 'Answered' END AS q1_status,
    CASE WHEN q2_rating IS NULL THEN 'Skipped' ELSE 'Answered' END AS q2_status,
    CASE WHEN q3_text IS NULL THEN 'Skipped' ELSE 'Answered' END AS q3_status,
    COUNT(*) AS participant_count
FROM survey_responses
GROUP BY q1_status, q2_status, q3_status
ORDER BY participant_count DESC;

Best Practices for NULL Values

1. Define NULL constraints carefully: Decide which columns should accept NULL values based on your data requirements.

2. Always use IS NULL/IS NOT NULL: Never use = NULL or != NULL for comparisons.

3. Handle NULL values in queries: Use functions like IFNULL(), COALESCE(), or CASE expressions to provide fallbacks.

4. Document NULL meanings: Make sure team members understand what NULL represents in each column (e.g., "unknown," "not applicable," "not yet provided").

5. Consider default values: Sometimes a default value is better than NULL for your application.

6. Test NULL handling thoroughly: Ensure your application correctly handles NULL values in both input and output.

Summary

NULL values in MySQL represent missing or unknown data. They require special treatment in SQL queries:

• Use IS NULL and IS NOT NULL for testing NULL values
• Functions like IFNULL() and COALESCE() help manage NULL values
• NULL affects different SQL operations uniquely, including JOINs and aggregate functions
• Understanding NULL behavior is crucial for accurate query results

By mastering NULL value handling, you'll write more robust queries and avoid common pitfalls associated with missing data.

Exercises

1. Create a table with columns that have different NULL constraints and experiment with inserting valid and invalid data.

2. Write a query using COALESCE() to provide fallback values for multiple potentially NULL columns.

3. Compare the results of COUNT(*) and COUNT(column_name) on a table with NULL values.

4. Write a query that uses CASE expressions to categorize data based on whether values are NULL or not.

5. Create a report that includes percentages of NULL values for each column in a table.

Additional Resources

• MySQL Documentation on NULL Values
• SQL NULL Functions: IFNULL, COALESCE, NVL
• Database Design: To NULL or Not To NULL