The World of MySQL & MariaDB

MySQL NULL Check Guide: SQL Techniques and Examples

SQL Basics
目次

1. Introduction: The Importance of MySQL NULL Evaluation

What is NULL?

MySQL’s NULL means that data “does not exist.” NULL is different from “blank” or “zero” and represents an indeterminate value in the database. NULL indicates that a value has not yet been entered or that data is missing, so special care is needed in database design and query operations. For example, in a customer database, if the “phone number” column is NULL, it means the customer has not provided a phone number or it hasn’t been entered yet. NULL is often mistakenly thought of as “empty,” but it actually has a special meaning distinct from whitespace characters or zero.

The Importance of NULL Evaluation

Mishandling NULL can cause database queries to not work as expected. For instance, when setting conditions in SQL, using operators without properly checking for NULL can return incorrect results. This can lead to unexpected errors or bugs, making it crucial to understand NULL evaluation correctly and use it appropriately. Consider the following SQL statement.
SELECT * FROM customers WHERE phone_number = NULL;
This query does not return the intended results because NULL cannot be compared with the “equals” operator. A special operator is required to evaluate NULL. Getting NULL evaluation wrong not only affects data retrieval but also impacts data integrity and reliability. Therefore, understanding how to handle NULL correctly in SQL is essential for database operations.

2. Basics of NULL Checks: Operators to Use in MySQL

IS NULL and IS NOT NULL Basics

In MySQL, you cannot use operators such as = (equal) or <> (not equal) to test for NULL. Instead, you use the operators IS NULL and IS NOT NULL.
  • IS NULL: Checks whether the column value is NULL.
  • IS NOT NULL: Checks whether the column value is not NULL.
For example, to find customers whose “phone number” is NULL in a customer database, you would write:
SELECT * FROM customers WHERE phone_number IS NULL;
This query returns all customers where phone_number is NULL. Conversely, to search for rows where it is not NULL, you use IS NOT NULL as follows.
SELECT * FROM customers WHERE phone_number IS NOT NULL;
Thus, when dealing with NULL, you must always use IS NULL or IS NOT NULL.

Differences Between NULL and Other Values (Empty String, Zero)

Although NULL, empty strings ('') and zero (0) may look similar, they have different meanings in a database.
  • NULL: Indicates that a value does not exist or is unknown.
  • Empty string (''): A string of length 0, meaning the data exists but is empty.
  • Zero (0): Represents the numeric value zero.
For example, consider the following query.
SELECT * FROM products WHERE price = 0;
This query finds products with a price of zero, but it does not return products with a NULL price. To find products where the price is NULL, you need to use IS NULL.
SELECT * FROM products WHERE price IS NULL;
Understanding this distinction is the first step toward correctly handling NULL checks.

3. Comparing NULL with Other Data Types: Commonly Overlooked Points

Differences Between NULL, Empty Strings, and Zero

When working with NULL in MySQL, it’s easy to confuse it with empty strings or zero, but they are distinct concepts. NULL means “no value exists,” an empty string is “a string of length zero,” and zero represents the numeric value zero.
  • NULL: Indicates that data does not exist or is unknown.
  • Empty string (''): Indicates that a string of length 0 exists.
  • Zero (0): Indicates that the numeric value is zero.
For example, consider comparing NULL with an empty string as follows.
SELECT * FROM users WHERE name = '';
This query returns users whose name is an empty string. However, to retrieve users whose name is NULL, write it as follows.
SELECT * FROM users WHERE name IS NULL;
Thus, NULL and empty strings must be treated as distinct.

Differences Between NULL and FALSE

NULL and FALSE are also often confused, but they differ. FALSE represents a logically false value, whereas NULL indicates the absence of a value. For example, when handling NULL and FALSE in conditions, be aware that the results differ.
SELECT * FROM users WHERE is_active = FALSE;
This query returns users who are not active, but users where is_active is NULL are not included in the results. To include NULLs in the search, you need to add a condition as follows.
SELECT * FROM users WHERE is_active IS NULL OR is_active = FALSE;
Because NULL and FALSE have different meanings, they must be used appropriately in SQL queries.

4. Practical NULL Checks: Techniques for Using Them in Queries

Checking NULL Across Multiple Columns

In practice, it is common for multiple columns to contain NULL. For example, in a table that manages customer information, fields such as “phone number” or “email address” may be NULL, requiring NULL checks across multiple columns. In practice, it’s common for multiple columns to contain NULL. For example, in a table that manages customer information, fields such as “phone number” or “email address” may be NULL, so you need to perform NULL checks on multiple columns. For example, if you want to find customers whose phone number or email address is NULL, you would write: For instance, if you want to find customers whose phone number or email address is NULL, you would write:
SELECT * FROM customers
WHERE phone_number IS NULL OR email IS NULL;
This query extracts customers where either the phone number or the email address is NULL. Conversely, to find customers where neither the phone number nor the email address is NULL, use the AND operator. This query extracts customers where either the phone number or the email address is NULL. Conversely, to find customers where neither the phone number nor the email address is NULL, use the AND operator.
SELECT * FROM customers
WHERE phone_number IS NOT NULL AND email IS NOT NULL;
Performing NULL checks on multiple columns is an essential technique for writing flexible SQL queries. Performing NULL checks on multiple columns is an essential technique for writing flexible SQL queries.

Using Aggregate Functions with NULL Consideration

When aggregating data that includes NULL values, standard aggregate functions (such as COUNT or SUM) ignore NULLs, so special handling is required. For example, COUNT(*) counts rows including NULL values, whereas COUNT(column_name) excludes NULL values from the count. When aggregating data that includes NULL values, standard aggregate functions (such as COUNT or SUM) ignore NULLs, so special handling is required. For example, COUNT(*) counts rows regardless of NULLs, whereas COUNT(column_name) excludes NULL values from the count. For example, to sum sales amounts while excluding products whose stock quantity is NULL, you would write: For example, to sum sales amounts while excluding products whose stock quantity is NULL, you would write:
SELECT SUM(sales_amount) 
FROM products 
WHERE stock_quantity IS NOT NULL;
If you want to include NULL values in the aggregation result, you can use the COALESCE function to replace NULL with a specific value. For instance, to treat NULL as 0, you can write: If you want to include NULL values in the aggregation result, you can use the COALESCE function to replace NULL with a specific value. For instance, to treat NULL as 0, you can write:
SELECT COALESCE(SUM(sales_amount), 0) 
FROM products;

Leveraging Conditional Logic with NULL

By using SQL’s CASE statement, you can branch logic based on data that contains NULL. For example, let’s write a query that displays “Unknown” when a product’s stock is NULL, and shows the actual quantity when it is not NULL. By using SQL’s CASE statement, you can branch logic based on data that contains NULL. For example, let’s write a query that displays “Unknown” when a product’s stock is NULL, and shows the actual quantity when it is not NULL.
SELECT product_name,
       CASE
           WHEN stock_quantity IS NULL THEN 'Unknown'
           ELSE stock_quantity
       END AS stock_status
FROM products;
In this query, when the stock quantity is NULL, “Unknown” is displayed; otherwise, the stock quantity is shown as is. Using the CASE statement allows flexible presentation of data that includes NULL. In this query, when the stock quantity is NULL, “Unknown” is displayed; otherwise, the stock quantity is shown as is. Using the CASE statement allows flexible presentation of data that includes NULL.

5. Best Practices for Handling NULL

Minimize the Use of NULL in Data Design

The most important point when dealing with NULL values is to minimize the situations where NULL is used during data design. It is recommended to avoid NULL whenever possible during database design and apply a NOT NULL constraint to columns where a value is required. For example, in a customers table, it is important to design so that required information such as “name” or “address” cannot be NULL. Add a NOT NULL constraint to required columns, and explicitly allow NULL for columns where it may be appropriate.
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15),
    email VARCHAR(100)
);
By adding a NOT NULL constraint to the name column, you ensure that the customer’s “name” is always provided.

Maintain Data Integrity

Even for columns that allow NULL, it is important to set appropriate default values. To maintain data integrity, consider using meaningful values such as “unset” or “0” instead of NULL. For example, even if a product’s “release date” column permits NULL, you can prevent inconsistencies by setting a default value such as “1900-01-01” when the date is not set.
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    release_date DATE DEFAULT '1900-01-01'
);
By setting meaningful default values instead of NULL, you maintain data integrity and make later NULL checks easier.

Performance Optimization

Queries that heavily use NULL can affect performance. In particular, when you frequently evaluate IS NULL or IS NOT NULL on columns that contain many NULLs, index optimization is required. Adding indexes to columns with many NULL values can degrade search performance, so careful index design is important.

6. FAQ: Resolving Common NULL-Related Questions

Q1: Does comparing NULL with the = operator not cause an error?

A1: No, it doesn’t raise an error, but comparing NULL with = or <> operators is not correct. NULL represents an “unknown value,” so standard comparison operators don’t behave as expected. To test for NULL, use IS NULL or IS NOT NULL.

Q2: How can I aggregate data that contains NULL values?

A2: When aggregating data that includes NULLs, you can use the COALESCE function to replace NULL with a default value (e.g., 0) or add IS NULL as a condition. This allows accurate aggregation even when NULLs are present.

Q3: Are there any considerations when storing NULL values in a database?

A3: NULL indicates that “data does not exist,” so it’s important to understand its meaning clearly and use it only when necessary. Overusing NULL can make data interpretation more difficult.

Q4: Can I index columns that contain NULL values?

A4: Yes, you can index columns that contain NULLs, but if many rows are NULL, the index’s efficiency may degrade. Especially when searches using IS NULL or IS NOT NULL are frequent, proper index design is required.

7. Summary: Using NULL Checks Correctly

Handling NULL properly in MySQL is an essential skill for operating a database accurately and efficiently. NULL represents “nonexistent data” and carries a special meaning distinct from other values. To evaluate NULL correctly, you should use IS NULL and IS NOT NULL, and be mindful of NULL handling from the data design stage. In practice, you’re expected to employ techniques that effectively use queries and aggregations involving NULL, maintaining and performance. For example, the COALESCE function to replace NULL values with a specific value, and flexible query designs that combine NULL checks are useful. By correctly checking for NULL and using it appropriately, the accuracy and efficiency of SQL queries improve dramatically. Apply the techniques introduced in this article to reduce database operation issues and aim for more reliable data management.