我的回答可能有点题外话，但我想强调一个当您将 INNER JOIN 变成 OUTER JOIN 时可能出现的问题.

My answer may be a bit off-topic, but I would like to highlight a problem that may occur when you turn your INNER JOIN into an OUTER JOIN.

在这种情况下，将谓词(测试条件)放在 ON 或 WHERE 子句上的最重要区别在于，您可以将 LEFT 或 RIGHT OUTER JOINS 转换为 INNER JOINS 而无需注意，如果您将表的字段放在 WHERE 子句中.

In this case, the most important difference between putting predicates (test conditions) on the ON or WHERE clauses is that you can turn LEFT or RIGHT OUTER JOINS into INNER JOINS without noticing it, if you put fields of the table to be left out in the WHERE clause.

例如，在表 A 和 B 之间的 LEFT JOIN 中，如果在 WHERE 子句中包含涉及 B 字段的条件，则结果集中很可能不会从 B 返回空行.您有效且隐含地将 LEFT JOIN 转换为 INNER JOIN.

For example, in a LEFT JOIN between tables A and B, if you include a condition that involves fields of B on the WHERE clause, there's a good chance that there will be no null rows returned from B in the result set. Effectively, and implicitly, you turned your LEFT JOIN into an INNER JOIN.

另一方面，如果在 ON 子句中包含相同的测试，将继续返回空行.

On the other hand, if you include the same test in the ON clause, null rows will continue to be returned.

以下面的查询为例:

SELECT * FROM A 
LEFT JOIN B
   ON A.ID=B.ID

该查询还将返回 A 中不匹配任何 B 的行.

The query will also return rows from A that do not match any of B.

进行第二个查询:

SELECT * FROM A 
LEFT JOIN B
WHERE A.ID=B.ID

第二个查询不会从 A 返回任何与 B 不匹配的行，即使您认为它会返回，因为您指定了一个 LEFT JOIN.那是因为测试 A.ID=B.ID 会将任何 B.ID 为空的行排除在结果集中.

This second query won't return any rows from A that don't match B, even though you think it will because you specified a LEFT JOIN. That's because the test A.ID=B.ID will leave out of the result set any rows with B.ID that are null.

这就是为什么我喜欢将谓词放在 ON 子句中而不是放在 WHERE 子句中.

That's why I favor putting predicates in the ON clause rather than in the WHERE clause.