我需要检索所有默认设置从设置表，但也抓取字符设置，如果存在的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包含默认值和字符值。
当前回答
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
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'
您可能会发现使用简单的子查询更容易理解
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'
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;
