使用 OR 的 LEFT JOIN

Question

我在想，如果左连接中的两个条件都为真，我该如何返回OR子句中最左边条件的结果。迄今为止，我找到的解决方案都涉及在SELECT语句中使用CASE语句，这意味着我将放弃OR子句。另一个解决方案涉及在ORDER BY中使用CASE语句。是否有其他解决方案可以减少CASE语句的使用？我之所以问这个问题是因为现在只有两个LEFT JOIN，但随着时间的推移会增加更多。

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'

- Andre

---

我不理解你的查询... lang.data和lang2.data将始终返回相同的内容。 - Martin

为什么你用相同的连接条件两次加入到语言中？ - Shannon Severance

抱歉，我在示例语句中没有包含'lang.type'。应该是 lang.type = '名称' 和 lang2.type = '描述'。 - Andre

@Martin L：如所述，lang.data和lang2.data不总是返回相同的结果。如果Lang中满足条件的行数为Nrows，且N>1，则查询将返回N^2行，其中Lang和Lang2在除了N个案例之外的所有情况下都是不同的语言行。 - Shannon Severance

@Andre，您说需要加入更多的左连接。这是否意味着除了 NAME 和 DESCRIPTION 之外还有其他 Language.Type 定义？或者您是指 Language.language 将扩展到包括不止 'fr' 和 'en' 吗？ - Shannon Severance

@Shannon Severance：还会有其他类型，例如标题、说明等。通常我只需要检查所需语言的数据是否存在，如果不存在，则返回默认语言的数据；在我的示例中，这是英语（en）。 - Andre

Answer 1

似乎您希望获取法语描述（如果有的话），否则使用英语作为备选。

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

或者这个：

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'

如果发现法语描述的概率很高（如果第一个子查询成功，则不会评估第二个子查询），则第一个查询更有效率。

在 SQL Server、Oracle 和 PostgreSQL 中，如果您有大量的法语描述，则这种方法可能更加高效：

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'

这个查询将使用一种高效的方法 (HASH JOIN 或 MERGE JOIN) 来连接法语描述，只有在必要时才会回退到英语描述。

对于 MySQL，第一个和第三个查询没有区别。

在所有系统中，创建一个基于 language (item, language) 的组合索引。

- Quassnoi

Answer 2

你可以将多个Or语句改为使用In子句...

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'

- Charles Bretana

---

你可能还可以从UDF中返回列表('fr'，'en')，以便稍后更轻松地进行扩展。 - Alex Peck

所有主要的关系型数据库管理系统对于提供字面常量的 ID = IN (const1, const2) 和 ID = const1 OR ID = const2 生成相同的计划。 - Quassnoi

Answer 3

也许你想要类似这样的东西：

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'

如果同时存在法语和英语数据，或者只有法语数据存在，则会返回法语数据。

- Tony Andrews

Answer 4

如果您想要法语翻译（如果有的话）并将英语作为备选选择，您可以为每种语言加入一次语言表：

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'

- Guffa

Answer 5

为什么不直接在WHERE子句中设置您的条件呢？

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')

- Chris Porter