php - 避免多个 LEFT JOIN

Question

有没有办法避免为表“social_mcouple”添加第二个 LEFT JOIN 来查询下面的 where social_members.m_id = social_mcouple.c_id？

$res = @mysql_query("SELECT *, DATE_FORMAT(m_lld,'%m/%d/%y') AS m_lld_formatted FROM social_members 
        LEFT JOIN social_member_types ON t_id=m_type WHERE m_user='".$en['user']."'");

Answer 1

如果总是有一个 social_mcouple 对应于 social_members，或者您只对有对应关系的行感兴趣，那么您可以使用 INNER JOIN。如果您需要所有的 social_members 而不管是否有相应的 social_mcouple 那么您将需要一个 LEFT JOIN。 LEFT JOIN 将为您提供 social_mcouple.* 设置为 NULL 的所有行，其中没有匹配项。

性能影响实际上取决于数据集的大小。

编辑:添加示例 UNION 查询。

$res = @mysql_query("
(SELECT social_members.*, social_member_types.*, DATE_FORMAT(m_lld,'%m/%d/%y') AS m_lld_formatted,
  NULL AS mcouple1, NULL AS mcouple2, NULL AS mcouple3
FROM social_members 
LEFT JOIN social_member_types ON t_id=m_type
WHERE m_user='".$en['user']."' AND m_type != 2)

UNION

(SELECT social_members.*, social_member_types.*, DATE_FORMAT(m_lld,'%m/%d/%y') AS m_lld_formatted,
    social_mcouple.mcouple1, social_mcouple.mcouple2, social_mcouple.mcouple3
FROM social_members 
LEFT JOIN social_member_types ON t_id=m_type
     JOIN social_mcouple ON social_members.m_id = social_mcouple.c_id
WHERE m_user='".$en['user']."' AND m_type = 2)
");