mysql NULL value in where in CLAUSE

Question

how to deal with NULL value in mysql where in CLAUSE

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 ?

Answer 1

Maybe this information from the MySQL Reference Manual helps:

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.

Answer 2

As by my understanding you want to pull every record with 1,2,3 and null value.

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.