LEFT JOIN using OR

Question

I was wondering how I would return the result of the left most condition in a 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 AS item
LEFT JOIN language AS lang ON item.id = lang.item AND (lang.language = 'fr' OR lang.language = 'en')
LEFT JOIN language AS lang2 ON item.id = lang2.item AND (lang2.language = 'fr' OR lang2.language = 'en')
WHERE item.part_number = '34KM003KL'

Answer 1

You could change the multiple Ors to use an In clause instead...

SELECT i.id, i.part_number, L1.data name, L2.data description 
FROM item i
   LEFT JOIN language L1 ON i.id = L1.item 
      AND L1.language In ('fr', 'en')
   LEFT JOIN language L2 ON i.id = L2.item 
      AND L2.language In ('fr', 'en')
WHERE i.part_number = '34KM003KL'

Answer 2

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)

Answer 3

Maybe you want something like this:

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

That will return the French data if both French and English exist, or if only French exists.

Answer 4

If you want the french translation if it exist and the english as a second hand choise, you can join the language table once for each language:

select
   item.id, item.part_number, isnull(fr.data, en.data) as name
from
   item
   left join language fr on fr.item = item.id and fr.language = 'fr'
   left join language en on en.item = item.id and en.language = 'en'
where
   item.part_number = '34KM003KL'

Answer 5

Why don't you just do your criteria in the WHERE clause?

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