MySQL – WHERE Clause to Select Another Column if NULL

MySQL

This is my mysql table

id      date_created                date_active
1       2014-06-07 18:24:38     NULL
2       2014-06-07 18:24:38     2014-06-07 18:24:38
3       2014-06-07 18:24:38     2014-06-07 18:24:38
4       2014-06-07 18:24:38     NULL
5       2014-06-07 18:24:38     NULL
6       2014-06-07 18:24:38     NULL
7       2014-06-07 18:24:38     2014-06-07 18:24:38
8       2014-06-07 18:24:38     2014-06-07 18:24:38
9       2014-06-07 18:24:38     2014-06-07 18:24:38
10      2014-06-07 18:24:38     NULL

How to set WHERE to select another column if the column is NULL?

SELECT * 
FROM TABLE 
WHERE date_active = 'value'.

If date_active is NULL it should use the date_created column.

Best Answer

Generally speaking you can use COALESCE

SELECT * 
FROM TABLE 
WHERE COALESCE(date_active,date_created) = 'value'

However this is not sargable. Depending on indexes available you may find the following

SELECT * 
FROM TABLE 
WHERE date_active = 'value' OR 
      (date_active IS NULL AND date_created = 'value')

Or even

SELECT * 
FROM TABLE 
WHERE date_active = 'value'
UNION ALL
SELECT * 
FROM TABLE 
WHERE  (date_active IS NULL AND date_created = 'value')

Performs better

Related Question