mysql NULL value in where in CLAUSE

user_74314 0
Viewed 14651

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 ?

where-clause null mysql
5 Answers

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.

user_6853520

Using UNION as a subquery in IN operator can get tableIds as a list and from that can get results with the NULL value.

eg:

SELECT * FROM
mytable 
WHERE mytable.id IN(
    SELECT mytable.id
    FROM mytable
    where mytable.field IS NULL
    UNION
    SELECT mytable.id 
    FROM mytable
    WHERE mytable.field IN(1,2,3)
    )
user_123872660
Related