CODEWITHSUNDEEP
mysqlsql
James
James

Reputation: 112000

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)

Upvotes: 58

Views: 70396

Answers (4)

Rasika
Rasika

Reputation: 1998

Use a CASE statement for the select.

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

Upvotes: 6

OMG Ponies
OMG Ponies

Reputation: 332791

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.

Upvotes: 147

pilcrow
pilcrow

Reputation: 58741

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

Upvotes: 4

dkretz
dkretz

Reputation: 37655

Check out the COALESCE function.

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

Upvotes: 5

Related Questions