“和"之间的区别和“在哪里"在联接中 [英] 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.

其次，在您使用左联接而不是内联接的情况下，结果实际上有所不同.如果您将第二个条件作为连接的一部分包括在内，则在条件失败的情况下仍将获得结果行-您将从左表中获取值，并从右表中获取空值.如果将条件作为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(与):

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屋！