How Does MYSQL\'s IN Keyword Handle NULL Values in Comparisons?

MYSQL IN Keyword's Treatment of NULL Values

SQL queries often utilize the IN keyword to filter results based on a set of specified values. However, when dealing with NULL values, MYSQL's IN keyword exhibits a unique behavior that requires consideration.

Problem: IN Keyword Excluding NULL Values

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 intends to exclude rows with Error values of 'Timeout' or 'Connection Error,' but it unexpectedly omits rows where Error is NULL. To account for NULL values, an additional condition (OR Error is NULL) must be added.

Reason for NULL Exclusion

The IN keyword compares expressions using boolean values (1/0). However, in some cases, it can return NULL instead of boolean values. When Error is NULL, the IN expression becomes:

Error <> 'Timeout' AND Error <> 'Connection Error'

Null comparison rules apply here. As the value of Error is NULL, the expression cannot evaluate to either true or false.

Solutions to Include NULL Values

To include rows with NULL values, one can use the following solutions:

• COALESCE with IN:

  COALESCE(Error,'') not in ('Timeout','Connection Error');

• OR Condition with IS NULL:

  Error IS NULL OR Error not in ('Timeout','Connection Error');

• CASE Expression with Short-Circuiting:

  CASE WHEN Error IS NULL THEN 1
   ELSE Error not in ('Timeout','Connection Error') THEN 1
   END = 1

Example

Consider the following data:

create table tbl(msg varchar(100) null, description varchar(100) not null);
insert into tbl values('hi', 'greet'), (null, 'nothing');

The query:

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

Will return only 'hi' because the NOT IN expression evaluates to NULL for the row with NULL msg value.

Conclusion

The IN keyword in MYSQL treats NULL values as a special case during comparisons. Developers using IN must be aware of this behavior and implement appropriate handling to avoid excluding or misinterpreting rows with NULL values.

The above is the detailed content of How Does MYSQL\'s IN Keyword Handle NULL Values in Comparisons?. For more information, please follow other related articles on the PHP Chinese website!