MySQL - 如果在一个表中匹配另一个表，则排除所有来自第二个表的行

Question

抱歉，问题描述不够清晰，但我的理解是您有三个不同的表格：包含用户信息的一个表格，用户帖子的一个表格，以及用户点赞的一个表格。您想从包含用户数据和帖子的表格中选择数据，但只返回用户没有点赞过的帖子且不是用户本人发布的帖子。您尝试使用不同的 JOINS 组合，但未成功。

例如，您想选择 id = 1 的用户行。

Table users:
+----+----------+
| id | username |
+----+----------+
|  1 |        A |
|  2 |        B |
|  3 |        C | 
+----+----------+

Table posts:
+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
|  2 |       1 |
|  3 |       2 | 
|  4 |       3 | 
|  5 |       2 | 
|  6 |       3 | 
+----+---------+

Table likes:
+----+---------+---------+
| id | post_id | user_id |
+----+---------+---------+
|  1 |       3 |       2 |
|  2 |       3 |       1 |
|  3 |       4 |       1 |
|  4 |       1 |       3 |
+----+---------+---------+

Result wantend:
+---------+----------+
| post_id | username |
+---------+----------+
|       5 |        B |
|       6 |        C |
+---------+----------+

我遇到的问题是，我的查询结果也返回了post_id: 3，因为user_id: 2点赞了这篇文章。

希望你能理解我的问题。

谢谢您的帮助！ /Andreas

- user3195845

---

你在问题中提到的查询是什么？请编辑问题并添加此查询。 - Gordon Linoff

Answer 1

我认为你的数据模型不太正确，如果“点赞”一篇帖子会向“帖子”表添加一个用户。

然而，为了回答你最初的问题，你可以通过以下方式排除“已赞”的帖子：

SELECT p.post_id, p.user_id FROM 
    post p LEFT JOIN 
    likes l 
    ON p.post_id = l.post_id WHERE l.post_id IS NULL;

- Daniel

---

这就是答案。其他方法使用子查询，这会对性能造成重大影响，并且在大型数据库上使用时很危险。 - felwithe

这是最佳答案！我在一个大数据集上使用类似的方法进行多个连接。子查询需要执行4.3900秒，而这个巧妙的技巧只需要1.2109秒，这是一个巨大的差异！ - Max Ahmed

这是大多数情况下的正确答案，但在极少数情况下，选择的答案可能会显著提高性能，尤其是当连接字段未经过优化时。 - Lachezar Todorov

Answer 2

这里是一个使用 not exists 来查询 likes 的方法：

select p.id as post_id, u.username
from posts p join
     users u
     on p.user_id = u.id
where not exists (select 1
                  from likes l
                  where l.post_id = p.id and l.user_id = 1
                 ) and
      u.id <> 1;

- Gordon Linoff

---

在某些情况下，这个答案会产生更好的结果。这实际上取决于数据库的设计和大小。 - Lachezar Todorov

谢谢您分享的智慧，我之前不知道可以在子查询中使用主查询的字段来进行WHERE筛选！ - jerclarke

Answer 3

要查找在另一张表中没有匹配的行，请使用 LEFT JOIN，然后选择外键为 NULL 的行。

SELECT p.id as post_id, u.username
FROM posts p
LEFT JOIN likes l ON l.post_id = p.id AND l.user_id = 1
JOIN users u
WHERE u.id != 1 and l.post_id IS NULL

- Barmar

Answer 4

SELECT  p.id, u.username

FROM    stackoverflow.posts p
JOIN    stackoverflow.users u ON p.user_id = u.id

WHERE   user_id <> 1
        AND 
        p.id NOT IN 
        (
            SELECT  likes.post_id 

            FROM    stackoverflow.likes 

            WHERE   user_id = 1
        );

- user3315862