我需要检索所有默认设置从设置表，但也抓取字符设置，如果存在的x字符。
但是这个查询只检索那些字符= 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'
所以我应该需要这样的东西:
array(
'0' => array('somekey' => 'keyname', 'value' => 'thevalue'),
'1' => array('somekey2' => 'keyname2'),
'2' => array('somekey3' => 'keyname3')
)
其中键1和键2是默认值，而键0包含默认值和字符值。
当前回答
根据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在右边的表上应用筛选器。
其他回答
根据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在右边的表上应用筛选器。
您可能会发现使用简单的子查询更容易理解
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'
如果我正确理解你的问题，你想从设置数据库记录，如果他们没有连接到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
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'
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.
