SQL多表关联查询如何返回无匹配表数据行并取用户最新访问评论记录

你现有语句只返回1条数据的核心原因是：WHERE (users.id,visitDate,commentDate) IN (...) 条件中，无评论用户的commentDate为NULL，SQL中NULL值无法通过IN子句完成等值匹配，导致这部分数据被过滤。

完全可以通过单条SQL实现需求，以下是两种兼容不同数据库版本的写法：

版本1：支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/Oracle等，推荐） #

SELECT 
    u.*,
    -- 按需指定访问表字段，避免和其他表id重名
    v.id AS visit_id, v.visitdate,
    -- 按需指定评论表字段，无评论时自动返回NULL
    c.id AS comment_id, c.commentdate
FROM users u
-- 关联指定事件的最新访问记录
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY visitdate DESC) AS rn
    FROM visits 
    WHERE eventid = 16
) v ON u.id = v.userid AND v.rn = 1
-- 关联指定事件的最新评论记录
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY commentdate DESC) AS rn
    FROM comments 
    WHERE eventid = 16
) c ON u.id = c.userid AND c.rn = 1
-- 过滤出至少有访问记录/评论记录的用户
WHERE v.id IS NOT NULL OR c.id IS NOT NULL

版本2：兼容低版本MySQL（无窗口函数支持场景） #

SELECT 
    u.*,
    v.*,
    c.*
FROM users u
-- 关联最新访问记录
LEFT JOIN visits v ON v.userid = u.id AND v.eventid = 16
LEFT JOIN (
    SELECT userid, MAX(visitdate) AS max_visit_date
    FROM visits WHERE eventid = 16
    GROUP BY userid
) max_v ON v.userid = max_v.userid AND v.visitdate = max_v.max_visit_date
-- 关联最新评论记录
LEFT JOIN comments c ON c.userid = u.id AND c.eventid = 16
LEFT JOIN (
    SELECT userid, MAX(commentdate) AS max_comment_date
    FROM comments WHERE eventid = 16
    GROUP BY userid
) max_c ON c.userid = max_c.userid AND c.commentdate = max_c.max_comment_date
-- 过滤有效数据
WHERE (v.id IS NOT NULL OR c.id IS NOT NULL)
GROUP BY u.id

内容的提问来源于stack exchange，提问作者livto