“和"之间的区别和“哪里"在连接中 [英] Difference between "and" and "where" in joins

问题描述

和

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) 

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

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

推荐答案

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

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.

在这种情况下，WHERE 子句几乎肯定是您的意思，因此您应该使用它.

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

其次，在您使用 LEFT JOIN 而不是 INNER JOIN 的情况下，结果实际上存在差异.如果您将第二个条件作为连接的一部分包含在内，如果条件失败，您仍将获得结果行 - 您将从左表中获得值，右表中获得空值.如果您将条件作为 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.

这里有一个例子来证明这一点.

Here is an example to demonstrate this.

查询 1(WHERE):

Query 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):

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

这篇关于“和"之间的区别和“哪里"在连接中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！