LEFT JOIN using OR

Question

I was wondering how I would return the result of the left most condition in OR clause used in a LEFT JOIN if both evaluate to be true.

The solutions I've come upon thus far both involve using CASE statement in the SELECT, this does mean I'd abandon the OR clause.

The other solution involved using a CASE statement in an ORDER BY.

Is there any other solutions that would cut down on the use of CASE statements. Reason I ask is because as or now there's only two LEFT JOINs but over time more will be added.

SELECT item.id,
       item.part_number,
       lang.data AS name,
       lang2.data AS description
  FROM item
       LEFT JOIN
       language lang
          ON     item.id = lang.item
             AND (lang.language = 'fr' OR lang.language = 'en')
       LEFT JOIN
       language lang2
          ON     item.id = lang2.item
             AND (lang2.language = 'fr' OR lang2.language = 'en')
 WHERE item.part_number = '34KM003KL'

Answer 1

Seems you want a French description if it exists, otherwise fallback to English.

SELECT  item.id,
        COALESCE(
        (
        SELECT  lang.data
        FROM    language l
        WHERE   l.item = i.id
                AND l.language = 'fr'
        ),
        (
        SELECT  lang.data
        FROM    language l
        WHERE   l.item = i.id
                AND l.language = 'en'
        )
        ) AS description
FROM    item i

, or this:

SELECT  item.id,
        COALESCE(lfr.data, len.data)
FROM    item i
LEFT JOIN
        language lfr
ON      lfr.item = i.id
        AND lfr.language = 'fr'
LEFT JOIN
        language len
ON      len.item = i.id
        AND len.language = 'en'

The first query is more efficient if the probability of finding French description is high (it will not evaluate the second subquery if the first one succeeds).

In SQL Server, Oracle and PostgreSQL this one will probably more efficient if you have lots of French descriptions:

SELECT  item.id,
        COALESCE(
        lfr.data,
        (
        SELECT  lang.data
        FROM    language l
        WHERE   l.item = i.id
                AND l.language = 'en'
        )
        ) AS description
FROM    item i
LEFT JOIN
        language lfr
ON      lfr.item = i.id
        AND lfr.language = 'fr'

This query will use an efficient method (HASH JOIN or MERGE JOIN) to join the French descriptions, and will fallback to English one only if necessary.

For MySQL, the 1st and the 3rd queries make no difference.

In all systems, create a composite index on language (item, language)