SELECT one column if the other is null
I want to select a2.date
if it's there, but if it's NULL
I want to select a1.date
(a2
is being left-joined). This:
SELECT a2.date OR a1.date
...
Simply returns a boolean result (as one would expect), how do I get the actual value of the non-null column though? (a2.date
is preferred, but if it's null then a1.date
)
Solution 1:
The ANSI means is to use COALESCE:
SELECT COALESCE(a2.date, a1.date) AS `date`
...
The MySQL native syntax is IFNULL:
SELECT IFNULL(a2.date, a1.date) AS `date`
...
Unlike COALESCE, IFNULL is not portable to other databases.
Another ANSI syntax, the CASE expression, is an option:
SELECT CASE
WHEN a2.date IS NULL THEN a1.date
ELSE a2.date
END AS `date`
...
It requires more direction to work properly, but is more flexible if requirements change.
Solution 2:
Use a CASE
statement for the select.
SELECT CASE WHEN a2.date IS NULL THEN a1.date
ELSE a2.date END AS mydate
Solution 3:
Check out the COALESCE function.
Takes a variable number of arguments and returns the first non-null one. It works fine with joins.