How Does MySQL Handle NULL Values in IN Expressions?

MySQL IN Keyword and NULL Values

The IN keyword in MySQL performs a comparison between a given expression and a list of values, returning a boolean result (TRUE/FALSE). However, in certain scenarios, MySQL handles NULL values in a distinct manner.

Consider the following query:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and Error not in ('Timeout','Connection Error');

This query aims to retrieve rows from Table1 that have a CurrentDateTime value greater than '2012-05-28 15:34:02.403504' and an Error value that is not equal to 'Timeout' or 'Connection Error'. Surprisingly, this query excludes rows with NULL values for Error.

Why Does MySQL Ignore NULL Values in IN Expressions?

MySQL treats NULL as an unknown or undefined value. When used in an IN expression, MySQL evaluates NULL as neither TRUE nor FALSE, resulting in an unknown result. Therefore, the IN expression evaluates to NULL itself.

Fixing the Query

To retrieve rows with NULL Error values as well, there are several approaches:

1. Use COALESCE to replace NULL values with a default value:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and COALESCE(Error,'') not in ('Timeout','Connection Error');

2. Use IS NULL and OR to explicitly check for NULL values:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and (Error IS NULL OR Error not in ('Timeout','Connection Error'));

3. Use CASE to assign a boolean result based on Error values:

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and CASE WHEN Error IS NULL THEN 1 ELSE Error not in ('Timeout','Connection Error') THEN 1 ELSE 0 END = 1;

These modifications ensure that rows with NULL Error values are included in the query results.

The above is the detailed content of How Does MySQL Handle NULL Values in IN Expressions?. For more information, please follow other related articles on the PHP Chinese website!