CODEWITHSUNDEEP
mysqlnullleft-join
François
François

Reputation: 171

In a Left Join, how to replace NULL by the default column value?

Easy use case: I have a Users and a Settings tables, I want to get the selected timezone for all users.

Hence my query:

select U.UserId, S.TimeZone
    from Users as U
    left join Settings as S on S.UserId = U.UserId;

However not all users have set a timezone which is why I do the left join instead of selecting from Settings. Which is also why I get NULL values for some users:

+--------+--------------------+
| UserId | TimeZone           |
+--------+--------------------+
|    494 | NULL               |
|    734 | Europe/Zurich      |
|    789 | America/New_York   |

Now I'd like to replace NULL values by the default value set for the TimeZone column:

select U.UserId, IFNULL(S.TimeZone, DEFAULT(S.TimeZone))
    from Users as U
    left join Settings as S on S.UserId = U.UserId;

But this doesn't work, I still get NULL for users who don't have settings. My guess is that this is caused by the fact that MySQL tries to get the default value for a column that doesn't exist (e.g., there's no column TimeZone for UserId 494).

Now how do I get the default column value instead of the NULL values. Is it possible?

Of course I could put the plain text default value in the IFNULL, but I don't want to have to update my queries if one day the default value is changed in MySQL.

Upvotes: 3

Views: 8057

Answers (2)

Arek Jablonski
Arek Jablonski

Reputation: 449

select U.UserId, COALESCE(S.TimeZone, DEFAULT(S.TimeZone)) TimeZone
    from Users as U
    left join Settings as S on S.UserId = U.UserId;

Upvotes: 3

fancyPants
fancyPants

Reputation: 51918

Pleaes have a try with this one:

SELECT UserId, IFNULL(sq.TimeZone, DEFAULT(Settings.TimeZone))
FROM (
    select U.UserId, S.TimeZone
    from Users as U
    left join Settings as S on S.UserId = U.UserId
) sq
LEFT JOIN (SELECT TimeZone FROM Settings LIMIT 1) Settings;

Upvotes: 0

Related Questions