MySQL NULL: The Beginner’s Guide

In MySQL, NULL represents the absence of a value or a missing value. It is important to distinguish NULL from an empty string (''), zero (0), or any other default value. NULL means that the value is unknown, undefined, or not applicable.

This guide covers the basics of NULL in MySQL, including how to handle NULL values in queries, insert and update operations, comparisons, and functions that deal with NULL values.

1. What is NULL?

In MySQL, NULL is used to represent a missing or unknown value in a database. When a column has a NULL value, it means that the value is not known, rather than being an empty string, zero, or any other specific value.

NULL vs. Other Values

NULL: Represents a missing, undefined, or unknown value.
Empty String (''): A value that is explicitly set to an empty string.
Zero (0): A numeric value equal to zero.
FALSE: A boolean value representing false.

Example:

SELECT NULL, '', 0, FALSE;

NULL is unknown, '' is an empty string, 0 is zero, and FALSE is a boolean value.

2. Inserting NULL Values

You can insert NULL values explicitly into a column, provided that the column allows NULL values (i.e., it is not defined with the NOT NULL constraint).

Example:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100) NULL,
    age INT NULL,
    PRIMARY KEY (id)
);

-- Inserting a NULL value into the 'age' column
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', NULL);

Important:

If a column has a NOT NULL constraint, you cannot insert NULL into that column.

3. Querying NULL Values

When querying for NULL values, you cannot use the usual comparison operators like = or != because NULL is not equal to anything, even another NULL. Instead, you use the IS NULL or IS NOT NULL operators.

Checking for NULL

-- Query for rows where the 'age' is NULL
SELECT * FROM users WHERE age IS NULL;

-- Query for rows where the 'age' is NOT NULL
SELECT * FROM users WHERE age IS NOT NULL;

Important:

SELECT * FROM users WHERE age = NULL; will not work as expected because NULL is not equal to any value, not even another NULL.

4. Updating NULL Values

You can update columns to NULL by using the UPDATE statement. Make sure the column allows NULL values (not NOT NULL).

Example:

-- Update the 'age' to NULL for the user with id 1
UPDATE users
SET age = NULL
WHERE id = 1;

5. NULL in Comparisons

Since NULL cannot be compared directly using = or !=, you must use special functions or operators to handle NULL comparisons.

IS NULL / IS NOT NULL

Use IS NULL or IS NOT NULL to check if a value is NULL.

SELECT * FROM users WHERE age IS NULL;    -- Rows where age is NULL
SELECT * FROM users WHERE age IS NOT NULL; -- Rows where age is not NULL

Comparison with NULL

If you attempt a comparison like age = NULL, the result will be unknown because NULL is not equal to anything, including itself. The correct way is to use IS NULL or IS NOT NULL.

6. NULL Handling Functions

MySQL provides several functions to handle NULL values. Some of the most useful ones include:

6.1 IFNULL()

The IFNULL() function allows you to replace NULL values with a specified value.

-- Replace NULL with a default value (e.g., 'Unknown')
SELECT IFNULL(age, 'Unknown') AS age FROM users;

✅ Output:
If age is NULL, it will be replaced with 'Unknown'.

6.2 COALESCE()

The COALESCE() function returns the first non-NULL value in a list of arguments.

SELECT COALESCE(age, 'Unknown') AS age FROM users;

✅ Output:
It will return the first non-NULL value (similar to IFNULL(), but with more options).

6.3 NULLIF()

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

SELECT NULLIF(age, 0) AS age FROM users;

✅ Output:
If age is 0, it will return NULL, otherwise, it will return the value of age.

7. NULL and Aggregate Functions

In MySQL, aggregate functions like COUNT(), SUM(), AVG(), and MAX() ignore NULL values. For example:

-- Count the number of non-NULL 'age' values
SELECT COUNT(age) FROM users;  -- Ignores NULL values

If you want to include NULL values in your calculations, use IFNULL() or COALESCE() to replace NULL with a default value.

8. NULL Constraints

When creating a table, you can define whether a column can accept NULL values or not by using the NOT NULL constraint.

Example:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NULL,    -- This column can have NULL values
    salary DECIMAL(10,2) NOT NULL
);

9. Conclusion

NULL is a fundamental concept in MySQL and SQL in general. It represents the absence of a value and behaves differently than other values such as zero or an empty string. By understanding how to handle NULL properly with IS NULL, IFNULL(), COALESCE(), and other related functions, you can manage missing data more effectively.

Key Takeaways:

Use IS NULL and IS NOT NULL for comparisons.
Functions like IFNULL(), COALESCE(), and NULLIF() help handle NULL values.
NULL values are ignored by aggregate functions.
Ensure proper column constraints to allow or disallow NULL values.

By mastering NULL, you can work with databases more flexibly and efficiently, ensuring your data operations are robust and error-free.

Souy Soeng

MySQL NULL: The Beginner’s Guide