连接中“和”和“where”之间的区别

Whats the difference between

什么区别

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

and

和

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

both return the same result and both have the same explain output

两者都返回相同的结果，两者都有相同的解释输出

5 个解决方案

#1

Firstly there is a semantic difference. When you have a join, you are saying that the relationship between the two tables is defined by that condition. So in your first example you are saying that the tables are related by cd.Company = table2.Name AND table2.Id IN (2728). When you use the WHERE clause, you are saying that the relationship is defined by cd.Company = table2.Name and that you only want the rows where the condition table2.Id IN (2728) applies. Even though these give the same answer, it means very different things to a programmer reading your code.

首先是语义差异。如果有连接，则表示两个表之间的关系由该条件定义。所以在你的第一个例子中，你说这些表是由cd.Company = table2.Name AND table2.Id IN（2728）相关联的。当您使用WHERE子句时，您说该关系由cd.Company = table2.Name定义，并且您只需要条件table2.Id IN（2728）适用的行。尽管这些答案给出了相同的答案，但对于程序员阅读代码来说，这意味着完全不同的事情。

In this case, the WHERE clause is almost certainly what you mean so you should use it.

在这种情况下，WHERE子句几乎肯定是你的意思所以你应该使用它。

Secondly there is actually difference in the result in the case that you use a LEFT JOIN instead of an INNER JOIN. If you include the second condition as part of the join, you will still get a result row if the condition fails - you will get values from the left table and nulls for the right table. If you include the condition as part of the WHERE clause and that condition fails, you won't get the row at all.

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

Here is an example to demonstrate this.

这是一个证明这一点的例子。

Query 1 (WHERE):

查询1（WHERE）：

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

Result:

结果：

field1
200

Query 2 (AND):

查询2（AND）：

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

Result:

结果：

field1
100
200

Test data used:

使用的测试数据：

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');

#2

SQL comes from relational algebra.

SQL来自关系代数。

One way to look at the difference is that JOINs are operations on sets that can produce more records or less records in the result than you had in the original tables. On the other side WHERE will always restrict the number of results.

查看差异的一种方法是JOIN是对集合的操作，可以在结果中生成比原始表中更多的记录或更少的记录。另一方面，WHERE将始终限制结果的数量。

The rest of the text is extra explanation.

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

---

For overview of join types see article again.

有关连接类型的概述，请再次阅读文章。

When I said that the where condition will always restrict the results, you have to take into account that when we are talking about queries on two (or more) tables you have to somehow pair records from these tables even if there is no JOIN keyword.

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

So in SQL if the tables are simply separated by a comma, you are actually using a CROSS JOIN (cartesian product) which returns every row from one table for each row in the other.

所以在SQL中如果表只是用逗号分隔，那么实际上你使用的是CROSS JOIN（笛卡尔积），它返回一个表中每一行的每一行。

And since this is a maximum number of combinations of rows from two tables then the results of any WHERE on cross joined tables can be expressed as a JOIN operation.

并且由于这是来自两个表的行的最大组合数，因此交叉连接表上的任何WHERE的结果可以表示为JOIN操作。

But hold, there are exceptions to this maximum when you introduce LEFT, RIGHT and FULL OUTER joins.

但是，当你引入LEFT，RIGHT和FULL OUTER连接时，这个最大值有例外。

LEFT JOIN will join records from the left table on a given criteria with records from the right table, BUT if the join criteria, looking at a row from the left table is not satisfied for any records in the right table the LEFT JOIN will still return a record from the left table and in the columns that would come from the right table it will return NULLs (RIGHT JOIN works similarly but from the other side, FULL OUTER works like both at the same time).

LEFT JOIN将左表中的记录与给定条件的记录与右表中的记录连接，但是如果连接条件，查看左表中的一行不满足右表中的任何记录，LEFT JOIN仍将返回来自左表和来自右表的列中的记录将返回NULL（RIGHT JOIN的工作方式类似但是从另一侧起，FULL OUTER同时起作用）。

Since the default cross join does NOT return those records you can not express these join criteria with WHERE condition and you are forced to use JOIN syntax (oracle was an exception to this with an extension to SQL standard and to = operator, but this was not accepted by other vendors nor the standard).

由于默认的交叉连接不返回那些记录，你不能用WHERE条件表达这些连接标准，并且你*使用JOIN语法（oracle是一个例外，它带有SQL标准和to =运算符的扩展，但这不是被其他供应商接受，也没有标准）。

Also, joins usually, but not always, coincide with existing referential integrity and suggest relationships between entities, but I would not put as much weight into that since the where conditions can do the same (except in the before mentioned case) and to a good RDBMS it will not make a difference where you specify your criteria.

此外，连接通常（但不总是）与现有的参照完整性一致，并建议实体之间的关系，但我不会给予那么多的重视，因为条件可以做同样的事情（除了前面提到的情况）和一个好的RDBMS在您指定标准时不会产生任何影响。

#3

• The join is used to reflect the entity relations
• 连接用于反映实体关系
• the where clause filters down results.
• where子句过滤掉结果。

So the join clauses are 'static' (unless the entity relations change),
while the where clauses are use-case specific.

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

#4

There is no difference. "ON" is like a synonym for "WHERE", so t he second kind of reads like:

没有区别。 “ON”就像“WHERE”的同义词，所以第二种类型的读取就像：

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

JOIN table2 WHERE cd.Company = table2.Name AND table2.Id IN（2728）

#5

There is no difference when the query optimisation engine breaks it down to its relevant query operators.

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