SELECT one column if the other is null

Question

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)

possible duplicate of [Conditional NOT NULL case SQL](http://stackoverflow.com/questions/4821031/conditional-not-null-case-sql) — nawfal, May 03 '13 at 13:20

score 145 · Accepted Answer · edited Aug 03 '17 at 14:10

145

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.

edited Aug 03 '17 at 14:10

Nisse Engström

4,738
23
27
42

answered Apr 18 '11 at 02:32

OMG Ponies

325,700
82
523
502

`COALESCE` is syntactic sugar for the equivalent `CASE` statement so it is kind of redundant to mention it here. To use an analogy, all SQL constraints can be expressed using a `SELECT COUNT(*)` (cardinality) query but if I pointed this out in every answer I posted that used a `FOREIGN KEY` it might become warying for all concerned. But a downvote for that would be a little harsh. – onedaywhen Apr 19 '11 at 10:10
2

@OMGPonies: Don't worry about the downvoters - or, in more current terms, "haters gonna hate." – Arvindh Mani Aug 03 '17 at 19:10

score 6 · Answer 2 · edited Aug 03 '17 at 19:07

6

Use a CASE statement for the select.

SELECT CASE WHEN a2.date IS NULL THEN a1.date
    ELSE a2.date END AS mydate

edited Aug 03 '17 at 19:07

Nisse Engström

4,738
23
27
42

answered Apr 18 '11 at 02:26

Rasika

1,980
13
19

score 5 · Answer 3 · answered Apr 18 '11 at 02:27

5

Check out the COALESCE function.

Takes a variable number of arguments and returns the first non-null one. It works fine with joins.

answered Apr 18 '11 at 02:27

dkretz

37,399
13
80
138

score 4 · Answer 4 · answered Apr 18 '11 at 02:29

4

SELECT COALESCE(a2.date, a1.date) ...

answered Apr 18 '11 at 02:29

pilcrow

56,591
13
94
135

SELECT one column if the other is null

4 Answers4

Linked