SQL 左加入 Where 子句

Left Join With Where Clause

提问by Sein Kraft

I need to retrieve all default settings from the settings table but also grab the character setting if exists for x character.

我需要从设置表中检索所有默认设置，但如果 x 字符存在，还需要获取字符设置。

But this query is only retrieving those settings where character is = 1, not the default settings if the user havent setted anyone.

但是此查询仅检索字符 = 1 的那些设置，如果用户尚未设置任何人，则不会检索默认设置。

SELECT `settings`.*, `character_settings`.`value`
FROM (`settings`)
LEFT JOIN `character_settings` 
ON `character_settings`.`setting_id` = `settings`.`id`
WHERE `character_settings`.`character_id` = '1'  

So i should need something like this:

所以我应该需要这样的东西：

array(
    '0' => array('somekey' => 'keyname', 'value' => 'thevalue'),
    '1' => array('somekey2' => 'keyname2'),
    '2' => array('somekey3' => 'keyname3')
)

Where key 1 and 2 are the default values when key 0 contains the default value with the character value.

其中，当键 0 包含带有字符值的默认值时，键 1 和 2 是默认值。

回答by Andomar

The whereclause is filtering away rows where the left joindoesn't succeed. Move it to the join:

该where子句过滤掉left join不成功的行。将其移至联接：

SELECT  `settings`.*, `character_settings`.`value`
FROM    `settings`
LEFT JOIN 
       `character_settings` 
ON     `character_settings`.`setting_id` = `settings`.`id`
        AND `character_settings`.`character_id` = '1'  

回答by OMG Ponies

When making OUTER JOINs (ANSI-89 or ANSI-92), filtration location matters because criteria specified in the ONclause is applied before the JOIN is made. Criteria against an OUTER JOINed table provided in the WHEREclause is applied after the JOIN is made. This can produce very different result sets. In comparison, it doesn't matter for INNER JOINs if the criteria is provided in the ONor WHEREclauses -- the result will be the same.

在进行 OUTER JOIN（ANSI-89 或 ANSI-92）时，过滤位置很重要，因为在进行 JOIN 之前ON应用条款中指定的标准。在进行 JOIN 之后，将应用针对子句中提供的 OUTER JOINed 表的标准。这会产生非常不同的结果集。相比之下，如果在or子句中提供了条件，则 INNER JOIN 无关紧要——结果将是相同的。WHEREONWHERE

  SELECT  s.*, 
          cs.`value`
     FROM SETTINGS s
LEFT JOIN CHARACTER_SETTINGS cs ON cs.setting_id = s.id
                               AND cs.character_id = 1

回答by Mark

If I understand your question correctly you want records from the settings database if they don't have a join accross to the character_settings table or if that joined record has character_id = 1.

如果我正确理解您的问题，您需要设置数据库中的记录，如果它们没有与 character_settings 表的连接，或者该连接的记录具有 character_id = 1。

You should therefore do

因此你应该做

SELECT `settings`.*, `character_settings`.`value`
FROM (`settings`)
LEFT OUTER JOIN `character_settings` 
ON `character_settings`.`setting_id` = `settings`.`id`
WHERE `character_settings`.`character_id` = '1' OR
`character_settings`.character_id is NULL

回答by RichardTheKiwi

You might find it easier to understand by using a simple subquery

您可能会发现使用简单的子查询更容易理解

SELECT `settings`.*, (
    SELECT `value` FROM `character_settings`
    WHERE `character_settings`.`setting_id` = `settings`.`id`
      AND `character_settings`.`character_id` = '1') AS cv_value
FROM `settings`

The subquery is allowed to return null, so you don't have to worry about JOIN/WHERE in the main query.

子查询允许返回空值，因此您不必担心主查询中的 JOIN/WHERE。

Sometimes, this works fasterin MySQL, but compare it against the LEFT JOIN form to see what works best for you.

有时，这在 MySQL 中工作得更快，但将其与 LEFT JOIN 形式进行比较，看看哪种方式最适合您。

SELECT s.*, c.value
FROM settings s
LEFT JOIN character_settings c ON c.setting_id = s.id AND c.character_id = '1'

回答by Dan

The result is correct based on the SQL statement. Left join returns all values from the right table, and only matching values from the left table.

根据 SQL 语句，结果是正确的。左连接返回右表中的所有值，并且仅返回左表中的匹配值。

ID and NAME columns are from the right side table, so are returned.

ID 和 NAME 列来自右侧表，因此返回。

Score is from the left table, and 30 is returned, as this value relates to Name "Flow". The other Names are NULL as they do not relate to Name "Flow".

分数来自左表，返回 30，因为该值与名称“流”相关。其他名称为 NULL，因为它们与名称“流”无关。

The below would return the result you were expecting:

以下将返回您期望的结果：

    SELECT  a.*, b.Score
FROM    @Table1 a
    LEFT JOIN @Table2 b
       ON a.ID = b.T1_ID 
WHERE 1=1
AND a.Name = 'Flow'

The SQL applies a filter on the right hand table.

SQL 对右侧表应用过滤器。

回答by P-Gn

For this problem, as for many others involving non-trivial left joins such as left-joining on inner-joined tables, I find it convenient and somewhat more readable to split the query with a withclause. In your example,

对于这个问题，对于涉及非平凡左连接的许多其他问题，例如内连接表上的左连接，我发现用with子句拆分查询更方便且更具可读性。在你的例子中，

with settings_for_char as (
  select setting_id, value from character_settings where character_id = 1
)
select
  settings.*,
  settings_for_char.value
from
  settings
  left join settings_for_char on settings_for_char.setting_id = settings.id;