MySQL WHERE Clause: Handling NULL Values

Understanding NULL in MySQL

NULL means unknown or missing data. It is different from zero or an empty string.

Because NULL is not a value, it cannot be compared using standard operators like = or <>.

• NULL is not equal to anything, even NULL itself.
• Comparisons with NULL using = or <> return NULL (unknown), not TRUE or FALSE.
• Special operators IS NULL and IS NOT NULL are used to test for NULL.

Using IS NULL and IS NOT NULL in WHERE Clause

To filter rows with NULL values, use the IS NULL operator in the WHERE clause.

Similarly, use IS NOT NULL to find rows where the column has any non-NULL value.

• Example: SELECT * FROM employees WHERE manager_id IS NULL;
• Example: SELECT * FROM employees WHERE manager_id IS NOT NULL;

Why Standard Comparison Operators Fail with NULL

Using = or <> to compare with NULL does not work as expected because NULL means unknown.

Any comparison with NULL results in NULL, which is treated as FALSE in WHERE clauses.

• Example: WHERE column = NULL returns no rows.
• Always use IS NULL or IS NOT NULL to check for NULL.

Examples of NULL Handling in WHERE Clause

Here are practical examples demonstrating NULL handling in MySQL queries.