Complete MySQL NULL Guide: Basics to Avoiding Issues

1. Introduction

• Beginners using MySQL for the first time
• Intermediate users who understand the basics of SQL and want to learn more deeply
• Engineers involved in database design or operations

• Correctly understand what NULL is
• Be able to manipulate and query data that includes NULL
• Learn best practices for avoiding issues related to NULL

2. NULL Basics

Definition of NULL

• NULL: No value exists (undefined state)
• Empty string (“”): The value exists but its content is empty
• Zero (0): The value exists and its value is “0”

Characteristics of NULL

1. Handling of NULL in comparison operations In SQL, NULL is handled with special rules. For example, you need to be careful about the results of comparison operations like the following.

   SELECT NULL = NULL; -- Result: NULL
   SELECT NULL <> NULL; -- Result: NULL
   SELECT NULL IS NULL; -- Result: TRUE

• Even when you compare NULL using regular comparison operators (=, <, >, etc.), the result is NULL.
• To evaluate NULL correctly, you need to use IS NULL or IS NOT NULL.

1. NULL in arithmetic operations Arithmetic operations that include NULL always return NULL. Example:

   SELECT 10 + NULL; -- Result: NULL
   SELECT NULL * 5; -- Result: NULL

3. Logical operations with NULL When a condition contains NULL, the result is also NULL. See the examples below.

   SELECT NULL AND TRUE; -- Result: NULL
   SELECT NULL OR FALSE; -- Result: NULL

NULL and Causes of Trouble

• Unexpected query results For example, the following query excludes rows where age is NULL.

  SELECT * FROM users WHERE age > 20;

  SELECT * FROM users WHERE age > 20 OR age IS NULL;

• Calculation errors or misinterpretation of blank data Aggregate functions (SUM, AVG, etc.) ignore NULL when calculating. Therefore, datasets with many NULLs can produce unexpected results.

Summary of Basic NULL Rules

• NULL represents a state where no value exists.
• Because regular comparison operators cannot handle it correctly, use IS NULL or IS NOT NULL.
• If NULL is involved in arithmetic or logical operations, the result is also NULL.

3. How to Work with NULL

How to Specify NULL When Inserting Data

• Explicitly Specify NULL

  INSERT INTO users (name, age) VALUES ('Taro', NULL);

• NULL as Default Value If NULL is set as the default value, omitting the column will automatically insert NULL.

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

  INSERT INTO users (name) VALUES ('Hanako');

How to Set NULL When Updating Data

• Update Value to NULL

  UPDATE users SET age = NULL WHERE name = 'Taro';

• Conditional Update You can add conditions to set NULL in specific situations.

  UPDATE users SET age = NULL WHERE age < 18;

How to Use NULL as a Condition When Deleting Data

• Delete Using NULL Condition

  DELETE FROM users WHERE age IS NULL;

• Delete with Multiple Conditions Including NULL

  DELETE FROM users WHERE age IS NULL AND name = 'Taro';

Precautions When Working with NULL

1. Use IS NULL Correctly When specifying NULL in a condition, always use IS NULL or IS NOT NULL instead of the = operator.

   SELECT * FROM users WHERE age = NULL; -- Incorrect
   SELECT * FROM users WHERE age IS NULL; -- Correct

2. Design Applications with NULL Handling in Mind When your application manipulates data, being mindful of NULL handling can prevent unintended behavior.
3. Leverage Transactions For data operations involving NULL, consider using transactions to avoid unintended data changes.

4. Searching Data Containing NULL

Basic Methods for Searching NULL

• Search for NULL

  SELECT * FROM users WHERE age IS NULL;

• Search for non-NULL values

  SELECT * FROM users WHERE age IS NOT NULL;

Searching with Complex Conditions Involving NULL

• When including NULL in a condition

  SELECT * FROM users WHERE age > 20 OR age IS NULL;

• NOT operator with NULL

  SELECT * FROM users WHERE NOT (age > 20 OR age IS NULL);

Using NULL with the LIKE Operator

SELECT * FROM users WHERE name LIKE '%a%';
-- Rows with NULL values are not matched by this condition

SELECT * FROM users WHERE name LIKE '%a%' OR name IS NULL;

Aggregate Functions and Searching NULL

• COUNT function

  SELECT COUNT(*) AS total_records, COUNT non_null_ages FROM users;

• COUNT(*): counts all records, including those with NULL
• COUNT(column): counts records where the column is not NULL
• Other aggregate functions

  SELECT AVG(age) AS average_age FROM users WHERE age IS NOT NULL;

Precautions When Searching for NULL

1. Difference between IS NULL and = Because NULL cannot be used with regular comparison operators, you must always use IS NULL or IS NOT NULL.

   SELECT * FROM users WHERE age = NULL; -- Incorrect
   SELECT * FROM users WHERE age IS NULL; -- Correct

2. Consideration in multiple conditions When NULL is present, if you don’t explicitly include NULL in the condition, you may get unintended results.

   SELECT * FROM users WHERE age > 20; -- NULL rows are excluded
   SELECT * FROM users WHERE age > 20 OR age IS NULL; -- includes NULL

3. Impact on performance When NULL is part of a condition, index usage may be limited. It’s recommended to verify index effectiveness.

EXPLAIN SELECT * FROM users WHERE age IS NULL;

Summary

5. NULL and Index Performance

Setting Indexes on Columns Containing NULL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX (age)
);

Using Indexes with IS NULL and IS NOT NULL

• When the index is applied

  SELECT * FROM users WHERE age IS NULL;

• When the index is not applied Using more complex conditions, as shown below, can prevent the index from being used.

  SELECT * FROM users WHERE age + 1 IS NULL;

NULL and Composite Indexes

• Example of a composite index

  CREATE TABLE employees (
      id INT AUTO_INCREMENT PRIMARY KEY,
      department_id INT,
      salary INT,
      INDEX (department_id, salary)
  );

NULL and Its Impact on Performance

1. Effectiveness of indexes

• Searches that include NULL often allow indexes to work efficiently. However, when the condition becomes complex, index usage may be limited.

1. Considerations for large data volumes

• Adding indexes to columns with many NULLs can increase index size and potentially degrade query performance.

1. Design to avoid NULL

• For columns that frequently contain NULL, setting a default value to reduce NULL usage can help improve performance.

Performance Optimization Tips

• Proper use of indexes Use EXPLAIN to verify whether an index is being applied.

  EXPLAIN SELECT * FROM users WHERE age IS NULL;

• Design to minimize NULL Apply a NOT NULL constraint and use default values to design data that avoids NULL:

  CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      age INT NOT NULL DEFAULT 0
  );

• Reevaluate indexes Add or drop indexes based on data volume and query patterns to optimize performance.

Summary

6. NULL and Sorting

Default Sort Order for NULL

• Ascending (ASC): NULL appears at the beginning.
• Descending (DESC): NULL appears at the end.

SELECT * FROM users ORDER BY age ASC;
-- NULL appears first

SELECT * FROM users ORDER BY age DESC;
-- NULL appears last

How to Position NULL in a Specified Order

• Place NULL at the beginning

  SELECT * FROM users ORDER BY age IS NULL DESC, age ASC;

• Place NULL at the end

  SELECT * FROM users ORDER BY age IS NULL ASC, age ASC;

Handling NULL in Multi-Column Sorting

• Sorting with multiple conditions

  SELECT * FROM users ORDER BY department_id ASC, age IS NULL DESC, age ASC;

1. department_id ascending
2. records where age is NULL
3. non-NULL age records in ascending order

Sorting and NULL Performance

• Verify Index Usage

  EXPLAIN SELECT * FROM users ORDER BY age ASC;

Things to Watch When Sorting

1. Consider the column’s data type

• If the data type of a column that can contain NULL isn’t appropriate, you may get unexpected results. Pay particular attention to the differences between numeric and string types.

1. Clarify sorting conditions

• Use IS NULL or IS NOT NULL to handle NULL explicitly so that query results are clear.

SELECT * FROM users WHERE age IS NULL ORDER BY age DESC;

Summary

7. Useful Functions for NULL

COALESCE Function

• Basic Syntax

  COALESCE(value1, value2, ..., valueN)

• Example

  SELECT COALESCE(age, 0) AS adjusted_age FROM users;

• Multiple-Argument Example

  SELECT COALESCE(NULL, NULL, 'Default Value', 'Other Value') AS result;

IFNULL Function

• Basic Syntax

  IFNULL(expression, alternate_value)

• Example

  SELECT IFNULL(age, 0) AS adjusted_age FROM users;

• Difference from COALESCE
• IFNULL handles only two arguments, whereas COALESCE can handle multiple arguments.

NULL-Safe Equality Operator (<=>)

• Example

  SELECT * FROM users WHERE age <=> NULL;

• Difference from the regular equality operator (=)
• With the = operator, NULL = NULL yields NULL, whereas the <=> operator yields TRUE.

ISNULL Function

• Basic Syntax

  ISNULL(expression)

• Example

  SELECT ISNULL(age) AS is_null FROM users;

NULLIF Function

• Basic Syntax

  NULLIF(expression1, expression2)

• Example

  SELECT NULLIF(salary, 0) AS adjusted_salary FROM employees;

Choosing a NULL Function

• When you want to set a default value: Use COALESCE or IFNULL
• When you want to compare NULL safely: Use the <=> operator
• When you want to explicitly test for NULL: Use ISNULL or IS NULL
• When you want to return NULL under specific conditions: Use NULLIF

Summary

8. Best Practices for Handling NULL

Handling NULL in Database Design

1. Deciding Whether to Allow NULL

• NULL indicates the absence of a value, but not every column needs to allow NULL.
• Example:
  • For required fields (e.g., username, email address), set a NOT NULL constraint.
  • For fields that may not have a value (e.g., intermediate score, optional settings), allow NULL.

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

2. Setting Default Values

• Setting appropriate default values helps minimize the use of NULL.

   CREATE TABLE orders (
       id INT AUTO_INCREMENT PRIMARY KEY,
       status VARCHAR(20) NOT NULL DEFAULT 'pending'
   );

Managing NULL on the Application Side

1. Validation During Data Entry

• When users submit data via input forms, verify that required fields have values.
• Add server‑side validation as well to prevent improper NULL insertion into the database.

1. Standardizing NULL Handling

• Maintain consistency in how NULL is handled across the application codebase.
• Example: Provide a helper function that converts NULL to a default value.

   def handle_null(value, default):
       return value if value is not None else default

Things to Watch for When Writing Queries

1. Safe Comparison of NULL

• When comparing NULL, always use IS NULL or IS NOT NULL.

   SELECT * FROM users WHERE age IS NULL;

2. Handling NULL in Complex Conditions

• In queries with multiple conditions, make the handling of NULL explicit.

   SELECT * FROM users WHERE age > 20 OR age IS NULL;

3. Considering Aggregates that Include NULL

• Aggregate functions (SUM, AVG, etc.) ignore NULL values during calculation. However, if you need to count NULLs, you must add a separate condition.

   SELECT COUNT(*) AS total_records, COUNT(age) AS non_null_records FROM users;

Design for Better Performance and Readability

1. Indexes and NULL

• When indexing columns that contain many NULLs, evaluate the index efficiency.
• Rebuild the index as needed.

1. Minimizing NULL

• By limiting NULL during the design phase, you improve database readability and performance.
• Instead, use specific default values or flags.

   CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       age INT NOT NULL DEFAULT 0
   );

Common Issues in the Field and How to Avoid Them

1. Issue: Unintended Query Results Due to NULL

• Solution: Use IS NULL and IS NOT NULL appropriately in queries.

   SELECT * FROM users WHERE name = 'Taro' OR name IS NULL;

2. Issue: Unexpected Behavior of Aggregate Functions

• Solution: Add a condition to exclude NULL.

   SELECT COUNT(age) FROM users WHERE age IS NOT NULL;

3. Issue: NULL and Data Integrity

• Solution: Enforce NOT NULL constraints at the database level and thorough validation on the application side.

Conclusion

9. Frequently Asked Questions (FAQ)

Q1: What is the difference between NULL, an empty string (“”), and zero (0)?

• A1:
• NULL: Indicates that a value does not exist (undefined).
• Empty string (“”): The value exists but its content is empty.
• Zero (0): The value exists and its numeric value is 0.
• Example: sql INSERT INTO users (name, age) VALUES ('Taro', NULL); -- age is NULL INSERT INTO users (name, age) VALUES ('Hanako', ''); -- age is an empty string INSERT INTO users (name, age) VALUES ('Jiro', 0); -- age is zero

Q2: Why doesn’t the result of NULL = NULL evaluate to TRUE?

• A2:
• According to SQL specifications, NULL represents an “unknown value”. Comparing unknown values yields an undefined result (NULL), not TRUE or FALSE.
• When comparing NULL, you must use IS NULL or IS NOT NULL.
• Example: sql SELECT NULL = NULL; -- result: NULL SELECT NULL IS NULL; -- result: TRUE

Q3: What should you watch out for when searching data that includes NULL?

• A3:
• When searching with NULL as a condition, using comparison operators (=, <, >, etc.) will not yield the expected results. Instead, use IS NULL or IS NOT NULL.
• Example: sql SELECT * FROM users WHERE age = NULL; -- incorrect SELECT * FROM users WHERE age IS NULL; -- correct

Q4: Are there any considerations regarding NULL and indexes?

• A4:
• You can index columns that contain NULL, but the efficiency of the index depends on the query conditions.
• Especially with complex conditions (e.g., involving expressions), the index may become ineffective.
• Checking the index: sql EXPLAIN SELECT * FROM users WHERE age IS NULL;

Q5: What is the difference between the COALESCE function and the IFNULL function?

• A5:
• COALESCE: Takes multiple arguments and returns the first non-NULL value.
• IFNULL: Takes only two arguments and returns the second if the first argument is NULL.
• Example: sql SELECT COALESCE(NULL, NULL, 'default value', 'other value'); -- result: 'default value' SELECT IFNULL(NULL, 'default'); -- result: 'default'

Q6: Are there any database design strategies to avoid NULL?

• A6:
• NOT NULL constraint: Add a constraint to prevent NULL in required fields.
• Default value setting: Use a default value instead of NULL.
• Example: sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, age INT NOT NULL DEFAULT 0 );

Q7: Can NULL affect the results of aggregate functions?

• A7:
• Aggregate functions (SUM, AVG, COUNT, etc.) ignore NULL when calculating. However, if you want to count the number of NULLs, you need to add a separate condition.
• Example: sql SELECT COUNT(*) AS total_records, COUNT(age) AS non_null_ages FROM users;

Q8: Can issues arise when joining data that contains NULL?

• A8:
• When joining on columns that contain NULL, NULL is considered not matching any value, which can lead to unexpected results.
• Solution: Write a query that accounts for NULL as a condition, or use the COALESCE function to replace NULL with a default value.

  SELECT *
  FROM table1 t1
  LEFT JOIN table2 t2 ON COALESCE(t1.key, 0) = COALESCE(t2.key, 0);

Summary

10. Summary

Review of Main Points

1. Basics and Characteristics of NULL

• NULL means “no value” or “unknown value” and is a special value distinct from an empty string (“”) or zero (0).
• In comparison operations, you must use IS NULL or IS NOT NULL to handle NULL safely.

1. Manipulating and Querying Data Containing NULL

• You learned how to correctly handle NULL when inserting, updating, deleting, and querying data.
• In particular, using syntax and functions such as IS NULL and COALESCE enables flexible and efficient operations.

1. NULL and Performance

• We explained how indexing columns that contain NULL affects performance and discussed data design strategies to optimize performance.
• By setting default values when appropriate, you can minimize the use of NULL.

1. Convenient Functions for Handling NULL

• Functions such as COALESCE, IFNULL, and NULLIF are useful for solving NULL-related issues.
• For safe comparisons, use the <=> operator to prevent unintended behavior.

1. Best Practices

• Designing to minimize NULL usage and applying proper validation on the application side helps maintain data integrity.
• Standardizing NULL handling in SQL queries improves code readability and maintainability.

Benefits of Understanding NULL

• Efficient Data Manipulation: Accurately handling NULL prevents unnecessary errors and enables writing efficient queries.
• Improved Data Integrity: Clarifying NULL usage policies during database design leads to higher data consistency.
• Enhanced Application Reliability: Properly handling NULL on the application side prevents unexpected behavior and bugs.

Next Steps

• Review how NULL is used in your own projects and identify areas for improvement.
• Use real datasets to experiment with functions and operators such as IS NULL, COALESCE, and IFNULL.
• Perform additional tuning related to indexing and performance.