sql - 在 WHERE 子句和 JOIN 条件中使用过滤条件有什么区别

Question

假设我有以下任何连接类型(INNER/LEFT/RIGHT/FULL)的连接查询

SELECT E.id, D.name, E.sal from EMP E
INNER/LEFT/RIGHT/FULL JOIN DEP D
ON E.id = D.id
AND E.sal > 100

此外，我有类似的 WHERE 条件查询

SELECT E.id, D.name, E.sal from EMP E
INNER/LEFT/RIGHT/FULL JOIN DEP D
ON E.id = D.id
WHERE E.sal > 100

上述查询的唯一区别是在 JOIN 和 WHERE 条件中使用 E.sal > 100。

在哪种情况下上述查询的结果会不同？还是两者总是相同？

Answer 1

对于 INNER JOIN，无论是在性能方面还是在结果集的定义方面，都没有区别。 OUTER JOIN 有区别。

WHERE 子句过滤 FROM 的结果 after FROM 被求值。因此，考虑这两个查询:

SELECT E.id, D.name, E.sal
from EMP E LEFT JOIN
     DEP D
     ON E.id = D.id
WHERE E.sal > 100;

SELECT E.id, D.name, E.sal
from EMP E LEFT JOIN
     DEP D
     ON E.id = D.id AND E.sal > 100;

第一个过滤 EMP 表以获取适当的 ID。第二个不过滤EMP 表。为什么不？好吧，LEFT JOIN 的定义表示获取第一个表中的所有行，而不管 ON 是否在第二个表中找到匹配的记录。所以，没有过滤。

现在，考虑这个版本:

SELECT E.id, D.name, E.sal
from EMP E RIGHT JOIN
     DEP D
     ON E.id = D.id
WHERE E.sal > 100;

SELECT E.id, D.name, E.sal
from EMP E RIGHT JOIN
     DEP D
     ON E.id = D.id AND E.sal > 100;

第一个将 RIGHT JOIN 转换为 INNER JOIN。为什么？因为对于不匹配的行，E.sal 是 NULL 并且无法通过 WHERE。第二个版本保留所有部门，甚至是那些没有员工符合条件的部门。注意:在遵循逻辑方面，我(和其他人)更喜欢左外连接而不是右外连接。 左 外连接的逻辑很简单:将所有行保留在第一个表中。

FULL OUTER JOIN 结合了这两种情况。