How to Handle NULL Values with IN Keyword in MySQL Queries?

MySQL IN Keyword Excludes NULL Values

When using the IN keyword in a MySQL query to filter rows based on specific values, unexpected results may occur when working with NULL values. This article investigates the reason behind this behavior and provides methods to handle NULL values correctly in such scenarios.

The query you mentioned:

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

surprisingly excludes rows where the Error column contains NULL values. This is because the IN keyword is semantically equivalent to:

Error <> 'TimeOut' AND Error <> 'Connection Error'

Due to the properties of NULL values, the above expression cannot evaluate to true. NULL values are not equal to any other value, including themselves. Therefore, rows with NULL values in the Error column are filtered out.

To include NULL values in the result set, you can adjust the query as follows:

• COALESCE(Error,'') not in ('Timeout','Connection Error'): This replaces NULL values in the Error column with an empty string before comparing them.
• Error IS NULL OR Error not in ('Timeout','Connection Error'): This explicitly checks for both NULL values and non-matching values.
• CASE WHEN Error IS NULL THEN 1 ELSE Error not in ('Timeout','Connection Error') THEN 1 END = 1: This uses a CASE statement to convert both NULL and matching values to 1, which is then checked for equality to 1.

An example:

create table tbl (msg varchar(100) null, description varchar(100) not null);

insert into tbl values ('hi', 'greet'), (null, 'nothing');

select 'hulk' as x, msg, description from tbl where msg not in ('bruce','banner');

This query will only return the row where msg is 'hi', because NULL values are excluded due to their indeterminacy.

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