如何基于第二张表条件查询数据？关联USERS和MESSAGES表查通信用户

你写的SQL存在两个核心问题：

• 没有增加限定「和指定unique_id有消息往来」的过滤条件，仅匹配了所有在消息表中当过发送方/接收方的用户，相当于查了所有产生过消息的用户，自然会返回多余结果
• 采用隐式内连接的写法（直接在FROM后写多个表）可读性差，也更容易出现逻辑疏漏，推荐使用显式JOIN语法

方案1：子查询+UNION（逻辑最清晰，性能稳定） #

先通过子查询找出所有和指定ID有往来的用户ID，再匹配用户表信息：

-- 请将下方的<指定的unique_id>替换为你实际要查询的目标ID
SELECT u.* 
FROM USERS u
WHERE u.unique_id IN (
    -- 查给指定ID发过消息的用户
    SELECT outgoing_user_id FROM MESSAGES WHERE incoming_user_id = <指定的unique_id>
    UNION
    -- 查收到过指定ID消息的用户
    SELECT incoming_user_id FROM MESSAGES WHERE outgoing_user_id = <指定的unique_id>
)

UNION会自动对两个查询的结果去重，不需要额外加DISTINCT或者GROUP BY。

方案2：显式JOIN实现 #

-- 请将下方的<指定的unique_id>替换为你实际要查询的目标ID
SELECT DISTINCT u.*
FROM USERS u
INNER JOIN MESSAGES m
ON 
  -- 指定ID是接收方，当前用户是发送方
  (m.incoming_user_id = <指定的unique_id> AND m.outgoing_user_id = u.unique_id)
  -- 指定ID是发送方，当前用户是接收方
  OR (m.outgoing_user_id = <指定的unique_id> AND m.incoming_user_id = u.unique_id)

用DISTINCT避免同一个用户和指定ID有多条消息往来时重复返回结果。

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