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

There is a MySQL function called COALESCE. It returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

If you for example run SELECT COALESCE(NULL, NULL, -1); you will get -1 back because it's the first non-NULL value.

So the trick here is to wrap your expression in COALESCE, and add a value as the last parameter that you also add in your IN function.

SELECT * FROM mytable WHERE COALESCE(field,-1) IN (1,2,3,-1)

It will only match if field is 1,2 or 3, or if field 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.