how to deal with NULL value in mysql where in CLAUSE
如何在CLAUSE中的mysql中处理NULL值
i try like
我尝试了
SELECT * FROM mytable WHERE field IN(1,2,3,NULL)
it not working
它不起作用
only work like :
只有这样工作：
SELECT * FROM mytable WHERE field IN(1,2,3) OR field IS NULL
how can i get it work in WHERE IN ? it is possible ?
我怎样才能让它在WHERE IN中运行？有可能的 ？
3 个解决方案
#1
7
As by my understanding you want to pull every record with 1,2,3 and null value.
根据我的理解，你想用1,2,3和null值拉每条记录。
I don't think its possible to put null in the IN operator. Its expects values and null is well.. not a value. So You really have to put the OR with the null to get the desired result.
我不认为可以在IN运算符中放置null。它期望值和null很好..不是值。因此，您必须将OR与null一起使用才能获得所需的结果。
#2
6
Maybe this information from the MySQL Reference Manual helps:
也许这些来自MySQL参考手册的信息有助于：
To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.
为了符合SQL标准，IN不仅在左侧的表达式为NULL时返回NULL，而且如果在列表中找不到匹配且列表中的一个表达式为NULL，则返回NULL。
#3
0
Following statement should help:
以下声明应有所帮助：
SELECT * FROM mytable WHERE COALESCE(field,0) IN (1,2,3,0)
#1
7
As by my understanding you want to pull every record with 1,2,3 and null value.
根据我的理解，你想用1,2,3和null值拉每条记录。
I don't think its possible to put null in the IN operator. Its expects values and null is well.. not a value. So You really have to put the OR with the null to get the desired result.
我不认为可以在IN运算符中放置null。它期望值和null很好..不是值。因此，您必须将OR与null一起使用才能获得所需的结果。
#2
6
Maybe this information from the MySQL Reference Manual helps:
也许这些来自MySQL参考手册的信息有助于：
To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.
为了符合SQL标准，IN不仅在左侧的表达式为NULL时返回NULL，而且如果在列表中找不到匹配且列表中的一个表达式为NULL，则返回NULL。
#3
0
Following statement should help:
以下声明应有所帮助：
SELECT * FROM mytable WHERE COALESCE(field,0) IN (1,2,3,0)