mysql - 当一张表没有行时 JOIN

Question

我有以下查询:

SELECT u.id, u.name, u.date_registered, p.time_created 
    FROM users u JOIN 
        prospect_notes p 
        ON u.id=p.subject_id  
    WHERE u.allocated_instructors = 668
    AND p.time_created = (SELECT MAX(p2.time_created) FROM prospect_notes p2 WHERE p2.subject_id = p.subject_id)
    ORDER BY p.time_created;

我的问题是，当 prospect_notes 表中没有与以下内容匹配的行时:

AND p.time_created = (SELECT MAX(p2.time_created) FROM prospect_notes p2 WHERE p2.subject_id = p.subject_id)

我没有得到任何结果。

相反，我希望返回users 表中的所有行(在这种情况下，p.time_created 可能为 NULL)。

Answer 1

由于 JOIN 子句，您需要小心。据推测，如果相关子查询没有匹配项，JOIN 中也没有匹配项。

因此，使用LEFT JOIN 并将逻辑移至FROM 子句:

SELECT u.id, u.name, u.date_registered, p.time_created 
FROM users u LEFT JOIN 
     prospect_notes p 
     ON u.id = p.subject_id LEFT JOIN
     (SELECT p2.subject_id, MAX(p2.time_created) as max_time_created
      FROM prospect_notes p2
      GROUP BY p2.subject_id
     ) p2
     ON p2.subject_id = p.subject_id AND p2.time_created = p.time_created
WHERE u.allocated_instructors = 668 
ORDER BY p.time_created;

也就是说，如果您只想从 prospect_notes 获取 time_created，则使用更简单的查询:

SELECT u.id, u.name, u.date_registered, MAX(p.time_created)
FROM users u LEFT JOIN 
     prospect_notes p 
     ON u.id = p.subject_id 
WHERE u.allocated_instructors = 668 
GROUP BY u.id  -- okay, assuming id is unique or a primary key
ORDER BY MAX(p.time_created);