mysql - 查询 "on"子句不限制加入？

Question

我有如下查询，它的意思是

Return id of all submissions into competition 2 including a count of how many times they have been read.

它做的一切都很好，除了它还返回所有其他比赛的行；为什么它不仅加入 where `competition_id` = 2？

SELECT 
    `c`.`competition_id`,
    `c`.`submission_id`,
    COUNT(
      `submission_reads`.`submission_id`
    ) `reads` 
  FROM
    `submission_reads` 
    RIGHT JOIN `competition_submissions` c 
      ON `c`.`submission_id` = `submission_reads`.`submission_id` 
      AND c.top_round = 1 
      AND c.`competition_id` = 2 
  GROUP BY `c`.`submission_id`

Answer 1

在 RIGHT 连接中，您从右侧的表中获取所有行； ON 子句限制了左侧表中的匹配行，即 submission_reads 表。如果您想限制 competition_submissions 的行数，您需要将联接从 RIGHT 更改为 LEFT。

comment: "I want all rows from the competition_submissions table regardless to whether it has an entry in the submission_reads"

然后您需要将 c.competition_id = 2 作为 WHERE 子句的一部分，因为您希望它在外部过滤“主”表的行加入:

SELECT 
    `c`.`competition_id`,
    `c`.`submission_id`,
    COUNT(
      `submission_reads`.`submission_id`
    ) `reads` 
  FROM
     `competition_submissions` c 
    LEFT JOIN `submission_reads` r
      ON `c`.`submission_id` = `r`.`submission_id` 
      AND `c`.top_round = 1 
  WHERE `c`.`competition_id` = 2 
  GROUP BY `c`.`submission_id`