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?

ypercubeᵀᴹ · Accepted Answer · 2011-07-06 10:52:18Z

4

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

edited Jul 6, 2011 at 10:52

answered Jul 6, 2011 at 10:41

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jack Murdoch · Accepted Answer · 2011-07-06 10:37:16Z

3

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

answered Jul 6, 2011 at 10:37

Jack Murdoch

2,8941 gold badge20 silver badges27 bronze badges

1 Comment

Fenton Over a year ago

+1 as this is a great answer - I had to go with COALESCE in the end as it will work on other databases too and performs about the same unless you use it as part of a sub-query.

VAShhh · Accepted Answer · 2011-07-06 10:56:21Z

2

Try the CASE...WHEN...THEN statement

SELECT 
    (CASE
       WHEN individual.Locale IS NULL THEN company.Locale
       ELSE individual.Locale
    END) 'Locale'
FROM
    individual
INNER JOIN
    company ON company.CompanyId = individual.CompanyId
WHERE
    individual.IndividualId = 1

edited Jul 6, 2011 at 10:56

answered Jul 6, 2011 at 10:38

VAShhh

3,5142 gold badges26 silver badges40 bronze badges

2 Comments

ypercubeᵀᴹ Over a year ago

This will not work, Change it into: CASE WHEN individual.Locale IS NULL THEN ...

ypercubeᵀᴹ Over a year ago

Yes, because CASE expression WHEN checks for equality and NULL=NULL is not True (unfortunately :)

Collectives™ on Stack Overflow

Two columns within an ISNULL function in MySql

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related