MySQL LEFT JOIN 不为丢失的行返回 NULL

Question

我可能搞砸了。但是我有一个 WordPress 系统，我试图在 MySQL 中导出特定用户数据。如果该行不存在，我希望元数据返回 NULL，但它似乎就像一个限制。如果任何元数据不存在，则省略整个用户。

SELECT
  users.ID,
  users.user_email,
  _first.meta_value as firstName,
  _second.meta_value as lastName,
  users.display_name,
  _gender.meta_value as gender,
  _age.meta_value as age,
  _nationality.meta_value as nationality

FROM kp_users

LEFT JOIN usermeta as _first ON kp_users.id = _first.user_id
LEFT JOIN usermeta as _second ON kp_users.id = _second.user_id
LEFT JOIN usermeta as _gender ON kp_users.id = _gender.user_id
LEFT JOIN usermeta as _age ON kp_users.id = _age.user_id
LEFT JOIN usermeta as _nationality ON kp_users.id = _nationality.user_id

WHERE
  _first.meta_key = 'first_name' AND
  _second.meta_key = 'last_name' AND
  _gender.meta_key = '_user_demographics_gender' AND
  _age.meta_key = '_user_demographics_age' AND
  _nationality.meta_key = '_user_demographics_nationality'

因此，对于元数据行，比方说，_user_demographics_age 行不存在，最终结果集中会忽略整个用户，我终究无法弄清楚原因。

Answer 1

您需要将其他条件放在 ON 子句中，而不是像下面的 Where 子句 -

SELECT
  users.ID,
  users.user_email,
  _first.meta_value as firstName,
  _second.meta_value as lastName,
  users.display_name,
  _gender.meta_value as gender,
  _age.meta_value as age,
  _nationality.meta_value as nationality

FROM kp_users

LEFT JOIN usermeta as _first ON kp_users.id = _first.user_id and _first.meta_key = 'first_name'
LEFT JOIN usermeta as _second ON kp_users.id = _second.user_id and _second.meta_key = 'last_name'
LEFT JOIN usermeta as _gender ON kp_users.id = _gender.user_id and _gender.meta_key = '_user_demographics_gender'
LEFT JOIN usermeta as _age ON kp_users.id = _age.user_id and _age.meta_key = '_user_demographics_age'
LEFT JOIN usermeta as _nationality ON kp_users.id = _nationality.user_id and _nationality.meta_key = '_user_demographics_nationality'