mysql - 如何在左连接查询中过滤主表

Question

我有 2 个表 POST 和 COMMENT，
每个帖子都有 ID、TITLE、CATEGORY_ID 和 USER_ID每条评论都有 ID、COMMENT、POST_ID 和 USER_ID

我想列出所有具有 category_id=2 和 comment.user_id=1 的帖子

CREATE TABLE post
    (`ID` int, `TITLE` varchar(9), `CATEGORY_ID` int, `USER_ID` int) 
;

INSERT INTO post
    (`ID`, `TITLE`, `CATEGORY_ID`, `USER_ID`)
VALUES
    (1, 'My post A', 1, 1),
    (2, 'My post B', 2, 1),
    (3, 'My post C', 2, 2)
;

CREATE TABLE comment
    (`ID` int, `COMMENT` varchar(12), `POST_ID` int, `USER_ID` int)
;

INSERT INTO comment
    (`ID`, `COMMENT`, `POST_ID`, `USER_ID`)
VALUES
    (1, 'My comment X', 1, 1),
    (2, 'My comment Y', 2, 1),
    (3, 'My comment Z', 1, 2)
;

此命令获取所有帖子，包括 category_id = 1 的帖子

    SELECT post.*, comment.comment, comment.post_id, comment.user_id c_user_id
FROM post
LEFT JOIN COMMENT
ON POST.id = COMMENT.post_id
AND COMMENT.user_id=1
AND POST.category_id =2

我明白了

+---------+-----------+-------------+---------+--------------+---------+-----------+
| USER_ID |   TITLE   | CATEGORY_ID | USER_ID |   COMMENT    | POST_ID | C_USER_ID | 
+---------+-----------+-------------+---------+--------------+---------+-----------+
| 1       | MY POST A | 1           | 1       | null         | null    | null      | 
| 2       | MY POST B | 2           | 1       | My Comment Y | 2       | 1         |  
| 1       | MY POST C | 2           | 2       | null         | null    | null      | 
+---------+-----------+-------------+---------+--------------+---------+-----------+

我想得到这个(所有帖子都有 category_id = 2 所以 2 条记录，其中出现了 user_id 的评论，其他评论为 null，如下所示:

+---------+-----------+-------------+---------+--------------+---------+-----------+
| USER_ID |   TITLE   | CATEGORY_ID | USER_ID |   COMMENT    | POST_ID | C_USER_ID |  
+---------+-----------+-------------+---------+--------------+---------+-----------+
| 2       | MY POST B | 2           | 1       | My Comment Y | 2       | 1         | 
| 1       | MY POST C | 2           | 2       | null         | null    | null      |  
+---------+-----------+-------------+---------+--------------+---------+-----------+

预先感谢您的帮助

Answer 1

有关在 left outer join 的 on 子句中放置条件的规则。 . .嗯，它适用于第二个 表的条件，而不是第一个。因此，将该条件放在 where 子句中:

SELECT post.*, comment.comment, comment.post_id, comment.user_id c_user_id
FROM post LEFT JOIN
     COMMENT
     ON POST.id = COMMENT.post_id AND COMMENT.user_id=1
WHERE POST.category_id = 2;

left outer join 的工作方式，从概念上讲就是:在第一个表中取一行。然后在第二个表中找到符合 on 条件的所有匹配行。保留所有火柴。如果没有匹配项，保留第一个表中的行。

猜猜看。这仍然是逻辑，即使条件过滤了第一个表上的行。因此，根据第一个表中的条件过滤 left outer join 无效。

所有这些同样适用于右外连接，所有表引用都颠倒了。