where子句过滤掉左连接未成功的行。将其移动到连接:

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

如果我正确理解你的问题，你想从设置数据库记录，如果他们没有连接到character_settings表，或者如果该连接记录有character_id = 1。

因此你应该这样做

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

在进行OUTER连接(ANSI-89或ANSI-92)时，过滤位置很重要，因为在进行连接之前应用了ON子句中指定的标准。在进行JOIN之后，应用WHERE子句中提供的针对OUTER连接表的标准。这可能产生非常不同的结果集。相比之下，如果在ON或WHERE子句中提供了条件，那么对于INNER join来说没有关系——结果将是相同的。

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

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

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`

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

有时，这在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'

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

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

Score来自左边的表，返回30，因为这个值与名称“Flow”相关。其他名称为NULL，因为它们与名称“流”不相关。

下面将返回您所期望的结果:

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

SQL在右边的表上应用筛选器。

对于这个问题，就像许多其他涉及非普通左连接的问题一样(比如内连接表上的左连接)，我发现用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;

The way I finally understand the top answer is realising (following the Order Of Execution of the SQL query ) that the WHERE clause is applied to the joined table thereby filtering out rows that do not satisfy the WHERE condition from the joined (or output) table. However, moving the WHERE condition to the ON clause applies it to the individual tables prior to joining. This enables the left join to retain rows from the left table even though some column entries of those rows (entries from the right tables) do not satisfy the WHERE condition.