我需要检索所有默认设置从设置表，但也抓取字符设置，如果存在的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包含默认值和字符值。
当前回答
在进行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
其他回答
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.
对于这个问题，就像许多其他涉及非普通左连接的问题一样(比如内连接表上的左连接)，我发现用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;
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
