mysql ifnull where

Question

i need such query

select * from t where
field=ifnull(:param, field) 'it not work's

so if param=NULL i have

select * from t where field is NULL

but if param =4 i have

select * from t where field=4

Answer 1

You can try this alternative this might help you

 select * from t where (field = NULL AND param= NULL) OR field ='4'

Answer 2

You can use the case when in where clause AFAIK bot not sure about MySQl,

But the better approach is to translate them,

you can read about that SQL WHERE clauses: Avoid CASE, use Boolean logic

So

select * from t where (:param is null and filed is null) or (filed = :param)

Answer 3

I think you are looking for NULLIF instead of ifnull

I think better approach would be to use CASE in where clause in your case.

Answer 4

When working with NULL you cannot use arithmetic operators. Try COALESCE to make a logical if with values integer or NULL

mysql> SELECT COALESCE(NULL,1);
    -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
    -> NULL