mysql NULL value in where in CLAUSE

how to deal wi开发者_高级运维th 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 ?

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.

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.

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.

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)
    )

Following statement should help:

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