连接中 "and" 和 "where" 的区别

Question

这两者有什么区别：

SELECT DISTINCT field1 
  FROM table1 cd  
  JOIN table2 
    ON     cd.Company = table2.Name 
       and table2.Id IN (2728) 

和

SELECT DISTINCT field1 
  FROM table1 cd  
  JOIN table2 
    ON cd.Company = table2.Name 
 where table2.Id IN (2728) 

两者返回相同的结果，并且两者具有相同的解释输出

- Midhat

---

小提示：看一下执行计划，这可能会提供更多的见解。SQL 进行了很多查询优化。 - Yvo

Answer 1

首先，这两种语法在语义上有所不同。使用JOIN时，您指定的条件定义了两个表之间的关系。因此，在您的第一个示例中，您正在指定表通过 cd.Company = table2.Name AND table2.Id IN (2728) 相关联。而使用WHERE子句，则表示关系是由 cd.Company = table2.Name 定义的，并且您只需要符合条件 table2.Id IN (2728) 的行。尽管两种方法得到的答案相同，但对于阅读您代码的程序员来说，它们意义完全不同。

在这种情况下，您几乎肯定是想要使用WHERE子句。

其次，如果您使用LEFT JOIN而不是INNER JOIN，实际上会有不同的结果。如果将第二个条件作为连接的一部分包含在内，则即使条件失败，仍将获得结果行-您将从左表获取值，并获得右表的null值。如果将该条件作为 WHERE 子句的一部分包含在内，且该条件失败，则根本不会获得该行。

以下是一个示例，以说明这一点。

查询1（WHERE）:

SELECT DISTINCT field1
  FROM table1 cd
  LEFT JOIN table2
    ON cd.Company = table2.Name
 WHERE table2.Id IN (2728);

结果：

field1
200

查询2（AND）:

SELECT DISTINCT field1
  FROM table1 cd
  LEFT JOIN table2
    ON cd.Company = table2.Name
   AND table2.Id IN (2728);

结果：

field1
100
200

使用的测试数据：

CREATE TABLE table1 (Company NVARCHAR(100) NOT NULL, Field1 INT NOT NULL);
INSERT INTO table1 (Company, Field1) VALUES
('FooSoft', 100),
('BarSoft', 200);

CREATE TABLE table2 (Id INT NOT NULL, Name NVARCHAR(100) NOT NULL);
INSERT INTO table2 (Id, Name) VALUES
(2727, 'FooSoft'),
(2728, 'BarSoft');

- Mark Byers

---

+1 提到了 LEFT JOIN。尽管如此，我还是会毫不羞耻地指向我的答案 https://dev59.com/SXE85IYBdhLWcg3w432o#2559769，可能有更多的细节和原因 :) - Unreason

在您的示例中（使用AND的查询2），如果我们有table1.Id IN（100）; 它仍然会返回两行..为什么会这样？... - psj01

@psj01，这是因为在该查询中使用了左连接的table1表，所以无论左连接条件如何，table1表的所有行都将包含在结果集中。 - tapasvi

Answer 2

SQL源自于关系代数。

从一个角度来看，JOIN是对集合进行操作的，可以产生比原始表格中更多或更少的记录。而WHERE则总是限制结果数量。

其余的文本是额外的解释。

---

有关连接类型的概述，请参见文章。

当我说where条件将始终限制结果时，您必须考虑到，当我们谈论两个（或更多）表上的查询时，即使没有JOIN关键字，您也必须以某种方式将这些表的记录配对。

因此，在SQL中，如果表格只是用逗号分隔，实际上使用的是CROSS JOIN（笛卡尔积），它为每个右表中的行返回一个左表中的所有行。

由于这是从两个表格中的行的最大组合，因此在交叉连接的表上的任何WHERE的结果都可以表示为JOIN操作。

但是要注意，当您引入LEFT、RIGHT和FULL OUTER连接时，会有例外。

LEFT JOIN将根据给定的条件将左表中的记录与右表中的记录连接起来，但是如果从左表中查看一行时，没有任何右表中的记录满足连接条件，则LEFT JOIN仍将返回左表中的记录，并在来自右表的列中返回NULL（RIGHT JOIN以相似的方式工作，但是从另一侧开始，FULL OUTER同时像两者一样工作）。

由于默认的CROSS JOIN不返回这些记录，因此您无法使用WHERE条件表示这些连接条件，而必须使用JOIN语法（Oracle是这个最大的例外，它使用SQL标准和=运算符的扩展，但其他供应商和标准都没有接受它）。

此外，连接通常与现有的引用完整性重合，并建议实体之间的关系，但我认为这并不重要，因为where条件可以做同样的事情（除了前面提到的情况），对于一个好的RDBMS，在哪里指定您的条件将不会有任何区别。

- Unreason

Answer 3

• 连接（join）用于反映实体之间的关系。
• where子句用于筛选结果。

因此，连接子句是“静态”的（除非实体关系发生变化），
而where子句是针对用例特定的。

- lexu

Answer 4

没有区别。"ON" 就像是 "WHERE" 的同义词，所以第二种写法可以理解为：

JOIN table2 WHERE cd.Company = table2.Name AND table2.Id IN (2728)

- Amy B

---

根据http://dev.mysql.com/doc/refman/4.1/en/join.html的说明，ON似乎不是WHERE的同义词。 - Josh Lee

我说它是“像”同义词。 - Amy B

仅针对连接操作和内部连接。 - nawfal

Answer 5

当查询优化引擎将其分解为相关的查询运算符时，它们之间没有任何区别。

- Johnno Nolan