如何编写SQL查询筛选与指定用户无共享聊天室的用户

我正在开发一款信使应用，需要筛选出不与指定用户X共享聊天室的用户，用来给用户推荐新的聊天对象。但我写不出正确的SQL查询，现在只能拿到完全没有聊天室的用户，以及有其他聊天室但同时和用户X共享聊天室的用户，没法准确得到目标用户。

简化后的MySQL数据库结构 #

--
-- Database: `chat`
--

-- --------------------------------------------------------

--
-- Table structure for table `participant`
--

CREATE TABLE `participant` (
  `id` int(11) NOT NULL,
  `room_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `participant`
--

INSERT INTO `participant` (`id`, `room_id`, `user_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 2),
(4, 2, 3);

-- --------------------------------------------------------

--
-- Table structure for table `room`
--

CREATE TABLE `room` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `room`
--

INSERT INTO `room` (`id`) VALUES
(1),
(2);

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `nickname` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `nickname`) VALUES
(1, 'nick 1'),
(2, 'nick 2'),
(3, 'nick 3'),
(4, 'nick 4');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `participant`
--
ALTER TABLE `participant`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_room_id` (`room_id`),
  ADD KEY `fk_user_id` (`user_id`);

--
-- Indexes for table `room`
--
ALTER TABLE `room`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `participant`
--
ALTER TABLE `participant`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `room`
--
ALTER TABLE `room`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `participant`
--
ALTER TABLE `participant`
  ADD CONSTRAINT `fk_room_id` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`),
  ADD CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);

我尝试过的错误查询 #

/* 我想排除与ID为1的用户有聊天的用户 */

/*  
    第一次尝试：会返回与目标用户有聊天的用户（错误），但能正确返回无聊天室及仅与他人聊天的用户
*/
SELECT DISTINCT p.room_id as p_room_id, target_user_participant.room_id as target_user_participant_room_id, u.id as user_id, u.nickname FROM user u
    LEFT JOIN participant p ON 
        u.id = p.user_id
    LEFT JOIN participant target_user_participant ON
        target_user_participant.user_id = 1
        AND
        p.room_id = target_user_participant.room_id
    WHERE u.id <> 1;
    
    
/*  第二次尝试：仅返回完全没有聊天室的用户 */
SELECT p.room_id, u.id as user_id, u.nickname, target_user_participant.user_id as target_user_id FROM user u
    LEFT JOIN participant p ON u.id = p.user_id
    LEFT JOIN participant as target_user_participant ON 
        target_user_participant.user_id = 1
        AND
        p.room_id = target_user_participant.room_id
    WHERE 
        p.room_id IS NULL;

    
/*  第三次尝试：排除了与目标用户的聊天记录，但仍返回与目标用户有聊天的用户（错误） */
SELECT p.room_id, u.id as user_id, u.nickname, target_user_participant.user_id as target_user_id FROM user u
    LEFT JOIN participant p ON u.id = p.user_id
    LEFT JOIN participant as target_user_participant ON 
        target_user_participant.user_id = 1
        AND
        p.room_id = target_user_participant.room_id
    WHERE 
        target_user_participant.user_id is null;

期望输出 #

当指定用户ID为1时，应仅返回ID为3和4的用户，因为ID为2的用户与ID为1的用户共享了一个聊天室。

解决方案 #

要实现需求，核心是排除所有和用户X在同一个聊天室的用户，以下三种方法都能达成目标：

方法1：NOT EXISTS子查询（逻辑最直观） #

直接判断当前用户是否不存在与目标用户共享的聊天室：

SELECT u.id, u.nickname
FROM user u
WHERE u.id != 1 -- 排除用户自己
  AND NOT EXISTS (
    SELECT 1
    FROM participant p1
    JOIN participant p2 ON p1.room_id = p2.room_id
    WHERE p1.user_id = u.id
      AND p2.user_id = 1
  );

方法2：LEFT JOIN + IS NULL #

通过左连接关联目标用户的聊天室，排除参与了这些聊天室的用户：

SELECT DISTINCT u.id, u.nickname
FROM user u
LEFT JOIN participant p ON u.id = p.user_id
LEFT JOIN participant x_p ON p.room_id = x_p.room_id AND x_p.user_id = 1
WHERE u.id != 1
  AND x_p.user_id IS NULL;

方法3：NOT IN子查询 #

先获取所有和目标用户共享聊天室的用户ID，再从用户表中排除这些ID：

SELECT u.id, u.nickname
FROM user u
WHERE u.id != 1
  AND u.id NOT IN (
    SELECT p2.user_id
    FROM participant p1
    JOIN participant p2 ON p1.room_id = p2.room_id
    WHERE p1.user_id = 1
      AND p2.user_id != 1
  );

以上三种查询都能正确返回用户3和4，完全符合需求。

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