Simple Mysql select another row if field is empty

Question

I have a multi-lingual table for the country like this:

--------------------------------------------------------------------
 country | lang | name
--------------------------------------------------------------------
 US      | en-US | United States
--------------------------------------------------------------------
 US      | fr    | États-Unis
--------------------------------------------------------------------
 US      | ar | الولايات المتحدة
--------------------------------------------------------------------
 US      | de |
--------------------------------------------------------------------

I want to select the field "name" for the country 'US' for the lang 'de' but if the name field is empty fallback to the language 'en-US'.

so will a query like this should be correct:

select name from countries where country='US' and name<>"" and lang in ('de', 'en-US')

I really need optimized query.

Gordon Linoff · Accepted Answer · 2014-01-26 22:09:29Z

1

Here is a way to get the answer:

select name
from countries
where country = 'US' and name<>"" and lang in ('de', 'en-US')
order by lang = 'de' desc
limit 1;

If you have an index on country, lang, name then it should perform well.

EDIT:

This might perform better with the same index:

select coalesce((select name
                 from countries
                 where country = 'US' and name <> "" and lang = 'de'
                ),
                (select name
                 from countries
                 where country = 'US' and name <> "" and lang = 'en-US'
                )
               ) as name

EDIT II:

A standard SQL approach similar to the first is:

select (case when sum(case when lang = 'de' then 1 else 0 end) > 0
             then max(case when lang = 'de' then name end)
             else max(name)
        end) 
from countries
where country = 'US' and name <> "" and lang in ('de', 'en-US');

An aggregation on two rows should be quite fast in any database. However, the coalesce() method should essentially just be two index lookups.

edited Jan 26, 2014 at 22:09

answered Jan 26, 2014 at 21:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

daliaessam Over a year ago

thanks Gordon for your answers, if I am looking for sql portability not only mysql, what would be the first query, and for the second query does coalesce sql protable, I know my question titled for mysql only but since I am learning would be interested to know about portability. I know for mssql it uses the "top" instead of "limit".

Gordon Linoff Over a year ago

@daliaessam . . . The second query is standard SQL and should work in most databases. The first uses limit; different databases have different ways of implementing this functionality.

Collectives™ on Stack Overflow

Simple Mysql select another row if field is empty

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related