在LEFT JOIN中，ON和WHERE有什么区别？

Question

以下SQL代码有何不同之处：

SELECT * FROM table_name;

SELECT column_name FROM table_name;

select count(*)
from A
left join B
on a.id = b.id
where a.status = 2 and
b.id is NULL

并且。

select count(*)
from A
left join B
on a.id = b.id
and a.status =2
where b.id is NULL

我读了这篇文章：连接查询之间的语义差异，但我仍然不知道哪个更好用。

我花了过去一个小时阅读这篇文章，并理解了所有答案的价值，但我最好地理解了代码示例和“从未遇到过这种情况”。

- edelwater

Answer 1

这段内容关键涉及到了LEFT JOIN的使用，一个会过滤结果，另一个仅会使LEFT JOIN失败，同时保留来自JOIN左侧的数据。

(1) left join B on a.id = b.id where a.status = 2

忽略其他过滤条件，这里表示要对表B进行LEFT JOIN操作，所以“尝试使用条件a.id=b.id与表B进行连接”。如果无法匹配，则保留左侧表（即A）的记录。在剩余的记录中，根据条件a.status=2过滤掉（即删除）不匹配的记录。

(2) left join B on a.id = b.id and a.status =2

忽略其他过滤条件，这里表示要在两个条件下对表B进行LEFT JOIN操作，因此“尝试使用两个条件a.id=b.id和a.status=2与表B进行连接”。如果两个条件都不符合（即使其中一个与B无关），仍然保留A的记录。

- RichardTheKiwi

Answer 2

区别在于条件的逻辑评估位置，这反过来会影响结果集。

在您的示例中（重新格式化），您有：

示例 1

SELECT COUNT(*)
  FROM A LEFT JOIN B ON a.id = b.id
 WHERE a.status = 2 AND b.id is NULL

示例2

SELECT COUNT(*)
  FROM A LEFT JOIN B ON a.id = b.id AND a.status = 2
 WHERE b.id is NULL

在第一个案例中，使用LEFT JOIN生成结果集; 然后在WHERE子句中过滤两个条件。

在第二个案例中，使用LEFT JOIN与a.status的筛选条件形成，并且在某些情况下可能会改变LEFT JOIN的结果集。然后，再通过主要的WHERE子句过滤这个结果集。

示例2基本上等效于： 示例2A

SELECT COUNT(*)
  FROM (SELECT * FROM A WHERE a.status = 2) AS A
  LEFT JOIN B ON a.id = b.id
 WHERE b.id is NULL

对于一些查询来说（但可能不是这个查询），差异很重要。

---

让我们尝试创建一些简单的样本数据：

Table A               Table B
id    status          id
4     2               1
5     3

示例1将有一个中间结果集：

a.id   a.status   b.id
4      2          null
5      3          null

WHERE子句可以消除第二行。

示例2将有一个中间结果集：

a.id   a.status   b.id
4      2          null

在这个例子中，最终结果是相同的，我无法找到任何数据不会得出相同结果的情况。

如果移动的查询条件在外部连接表上，并且较为复杂超过简单的相等比较，那么你可能会看到影响。

- Jonathan Leffler

---

我认为只有当它是内连接时，示例2才相当于那个。 - dotjoe

你评论中的“that”不太明确——假设你指的是使用FROM (SELECT * FROM A WHERE a.status = 2) AS A这个变量的情况作为“that”（我们称其为Example 2A），那么我向你提出挑战，让你提供一个样本数据集，在Example 2和Example 2A之间有所区别。 - Jonathan Leffler

@jonathan：根据你的数据集，示例2将具有一个中间（和最终）结果集，其中有2行，而不是1行。而示例2A将具有1行结果集。 - ypercubeᵀᴹ

我的理解是示例1等同于示例2A。 - ypercubeᵀᴹ

我不明白你如何将a.status = 2从左连接条件移动到子查询中，并使它相等。难道示例2的中间结果有两行，而示例2A只有一行吗？如果说什么的话，你的第二个例子（2A）和第一个例子是相等的吧？ - dotjoe

Answer 3

重新编写这两个查询，不使用LEFT JOIN / IS NULL，以便在某些情况下可以明显地返回不同的数据集：

---Query 1

SELECT COUNT(*)
FROM a
WHERE a.status = 2
  AND NOT EXISTS 
      ( SELECT *
        FROM b
        WHERE a.id = b.id
      )

并且

---Query 2

SELECT COUNT(*)
FROM a
WHERE NOT ( a.status = 2
        AND EXISTS 
            ( SELECT *
              FROM b
              WHERE a.id = b.id
            )
          )

--- or:

---Query 2
SELECT COUNT(*)
FROM a
WHERE a.status <> 2
   OR NOT EXISTS 
      ( SELECT *
        FROM b
        WHERE a.id = b.id
      )

- ypercubeᵀᴹ

---

我明白了，所以查询1更好。如果是“B.status=2”，那么它会在您的查询2示例1中的exist()子句中，因此（SELECT * from b WHERE a.id a= b.id AND b.status=2）是正确的吗？ - edelwater

查询1和查询2是不同的，它们返回不同的结果。查询1并不比查询2更好，但它更常见。我在实际情况中从未遇到过查询2类型。 - ypercubeᵀᴹ

是的，对于你的第二个问题，b.status=2 条件会有很大的变化。这是非常普遍的情况（如果要与左连接一起使用，则将该条件放在 ON 部分中）。 - ypercubeᵀᴹ

+1 这是一种清晰表示查询的方式。left join where null 风格的查询可能非常令人困惑！ - dotjoe

@dotjoe：是的。尤其是当你在 ON 子句中添加左表的条件时。 - ypercubeᵀᴹ

甚至更多，当该条件仅与主表相关时。 - dotjoe

Answer 4

也许这样更容易理解：

SELECT id
  FROM A
 WHERE status = 2
EXCEPT
SELECT id
  FROM B;

如果您只对结果的基数感兴趣，这种情况不太可能发生：

SELECT COUNT(*)
  FROM ( SELECT id
           FROM A
          WHERE status = 2
         EXCEPT
         SELECT id
           FROM B ) AS DT1;

- onedaywhen