MySQL IN Operator and Null Values
In MySQL, the IN operator is used to check whether a given value matches any value within a specified list. However, when using the IN operator with null values, unexpected behavior may occur.
Why NULL Values are Excluded
The MySQL IN operator returns a boolean result (TRUE or FALSE). When comparing a non-null value to null, the result is always FALSE. Consequently, when using IN to check if a value is not equal to any value in a list that includes null, all rows with null values are excluded from the result.
Fixing the Issue
To include null values in the result, the following options can be used:
Error = COALESCE(Error, '') NOT IN ('Timeout','Connection Error');
Error IS NULL OR Error NOT IN ('Timeout','Connection Error');
CASE WHEN Error IS NULL THEN 1 ELSE Error NOT IN ('Timeout','Connection Error') THEN 1 END = 1
SELECT t1.* FROM Table1 t1 LEFT JOIN ( SELECT Error FROM ErrorTable WHERE Error IN ('Timeout') ) t2 ON t1.Error = t2.Error;
Conclusion
When using the IN operator with null values, it is important to understand that the operator treats null as a special value. To include null values in the result, additional conditions or techniques, such as the ones mentioned above, must be employed.
The above is the detailed content of MySQL IN Operator and Null Values: Why Are Rows with Null Errors Excluded?. For more information, please follow other related articles on the PHP Chinese website!