Two columns within an ISNULL function in MySql

Question

I have two tables, one is for individuals and the other is for their company.

Each table has a column for locale, this is mandatory for the company, but not for the individual. The idea seems to be that if the individual doesn't set a preference, they are assumed to be in the locale of their company.

I would like to select the locale for the individual, using the company default if the individual locale is null and I thought of doing the following (which I don't think is possible in MySql)...

SELECT
    ISNULL(individual.Locale, company.Locale) `Locale`
FROM
    individual
INNER JOIN
    company ON company.CompanyId = individual.CompanyId
WHERE
    individual.IndividualId = 1

Is there a nice way to do this - or am I just going to end up sending both Locale's back and making the decision in the code?

Answer 1

You can use the COALESCE() function which returns the first non-NULL value among its arguments. This function can also be used in most other RDBMS like SQL-Server, Oracle 9, Postgres 8:

SELECT
    COALESCE(individual.Locale, company.Locale) AS Locale
FROM
    individual
INNER JOIN
    company ON company.CompanyId = individual.CompanyId
WHERE
    individual.IndividualId = 1

Answer 2

You pretty much had it, what you want is IFNULL()

SELECT
    IFNULL(individual.Locale, company.Locale) `Locale`
FROM
    individual
INNER JOIN
    company ON company.CompanyId = individual.CompanyId
WHERE
    individual.IndividualId = 1