Proper NULL Value Querying in MySQL: IS NULL vs = NULL Differences

The特殊性 of NULL Values

In relational databases, NULL represents missing or unknown values with special semantic meaning. According to Rule 3 of Codd's 12 rules for relational databases, database systems must be able to handle NULL values. NULL is not equal to any value, including itself, which means the evaluation of NULL = NULL always results in FALSE.

Correct NULL Value Query Syntax

In MySQL, to query records containing NULL values, you must use the IS NULL operator instead of the equality operator. For example:

SELECT pid FROM planets WHERE userid IS NULL

If using the equality operator:

SELECT pid FROM planets WHERE userid = NULL

This will always return an empty result set because NULL cannot be equal to any value, including NULL itself.

Difference Between NULL and Empty Strings

Many developers容易混淆 NULL values with empty strings. An empty string is a definite value - a string of length zero, while NULL represents the absence of a value. When querying:

-- Query empty strings
SELECT * FROM table WHERE column = ''

-- Query NULL values
SELECT * FROM table WHERE column IS NULL

These two queries target completely different data states.

NULL Value Handling in DATE/DATETIME Fields

In MySQL, NULL value handling for DATE and DATETIME fields has some special cases. According to MySQL documentation, for DATE and DATETIME columns declared as NOT NULL, you can use IS NULL to find the special date value '0000-00-00'. However, in practical use, developers might encounter fields显示为'0000-00-00' but cannot be queried using IS NULL.

This situation typically occurs when:

• Fields are defined as NOT NULL but default values of '0000-00-00' are inserted
• NULL values are converted to '0000-00-00' during database migration
• ODBC application compatibility considerations

Solutions and Best Practices

For NULL value query issues in DATE/DATETIME fields, the following solutions can be adopted:

Method 1: Using Custom Functions

CREATE FUNCTION NullDate(_Input DATETIME)
RETURNS BOOLEAN
BEGIN
    IF (_Input = '0000-00-00') OR
       (_Input = '0000-00-00 00:00:00') THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END

Usage:

SELECT * FROM table WHERE NullDate(date_column)

Method 2: Direct Query of Specific Values

SELECT * FROM table 
WHERE date_column = '0000-00-00' 
   OR date_column = '0000-00-00 00:00:00'

Impact of Database Engines

Different storage engines may have variations in NULL value handling:

• MyISAM: May have NULL value storage issues in certain versions
• InnoDB: Provides better NULL value support and transaction safety

If encountering NULL value query issues, consider converting the table to InnoDB engine:

ALTER TABLE table_name ENGINE=InnoDB

Practical Application Examples

Assume we have a user table where the last_login field may contain NULL values:

-- Create test table
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    last_login DATETIME NULL
);

-- Insert test data
INSERT INTO users VALUES 
(1, 'user1', '2023-01-01 10:00:00'),
(2, 'user2', NULL),
(3, 'user3', '0000-00-00 00:00:00');

-- Correctly query users who never logged in
SELECT * FROM users WHERE last_login IS NULL;

-- Query users with special date values
SELECT * FROM users WHERE last_login = '0000-00-00 00:00:00';

Conclusion

Properly handling NULL values is an essential skill in database development. Remember the key points: always use IS NULL and IS NOT NULL to query NULL values, avoiding the equality operator. For DATE/DATETIME fields, pay attention to MySQL's special handling methods, particularly in data migration and ODBC application integration scenarios. By understanding the nature of NULL values and MySQL's specific implementations, you can avoid common query errors and write more robust database applications.