SQL 表左连接时 where 子句和 on 子句有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8311096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the difference between where clause and on clause when table left join?
提问by SleeplessKnight
SQL1:
SQL1：
select t1.f1,t2.f2
from t1
left join t2 on t1.f1 = t2.f2 and t1.f2=1 and t1.f3=0
SQL2:
SQL2：
select t1.f1,t2.f2
from t1
left join t2 on t1.f1 = t2.f2
where t1.f2=1 and t1.f3=0
The difference is where and on clause, is there same return result? and what's the difference ? does DBMS run them in same way? thanks.
区别在于 where 和 on 子句，返回结果是否相同？有什么区别？DBMS 是否以相同的方式运行它们？谢谢。
回答by
The whereclause applies to the whole resultset; the on clauseonly applies to the join in question.
该where子句适用于整个结果集；onclause仅适用于有问题的连接。
In the example supplied, all of the additional conditions related to fields on the inner side of the join - so in this example, the two queries are effectively identical.
在提供的示例中，所有附加条件都与连接内侧的字段相关 - 因此在此示例中，两个查询实际上是相同的。
However, if you had included a condition on a value in the table in the outerside of the join, it would have made a significant difference.
但是，如果您在连接外侧的表中的某个值上包含了条件，则会产生显着差异。
You can get more from this link: http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause
您可以从此链接获得更多信息：http: //ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause
For example:
例如：
select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 and t2.f4=1
select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 where t2.f4=1
- do different things - the former will left join to t2 records where f4 is 1, while the latter has effectively been turned back into an inner join to t2.
- 做不同的事情 - 前者将离开连接到 t2 记录，其中 f4 为 1，而后者已有效地转回到 t2 的内部连接。
回答by Massimiliano Peluso
The first query is quicker than the second one as the join condition is more specific than the second one: it does not makes sense to return records that you will filter with the where clause (it would be better do not return them at all- query1)
第一个查询比第二个查询更快，因为连接条件比第二个更具体：返回将使用 where 子句过滤的记录没有意义（最好根本不返回它们 - query1 )
Anyway it really depends by the query optimizer.
无论如何，它真的取决于查询优化器。
have a look at the below:
看看下面的内容：
回答by Nicholas
The two queries are NOTidentical.
这两个查询不相同。
Mark Bannister was right in pointing out that the whereclause is applied to the whole result set but the on clauseapplies to the join.
Mark Bannister 正确地指出该where子句适用于整个结果集，而 onclause适用于连接。
In your case, for SQL 1 LEFT JOIN conditions filter joins on the right but the left side is always returned before any WHERE filtering. Since there are no WHERE conditions all of t1 is always returned.
在您的情况下，对于 SQL 1 LEFT JOIN 条件过滤器连接在右侧，但左侧总是在任何 WHERE 过滤之前返回。由于没有 WHERE 条件，因此始终返回所有 t1。
In SQL 2, the LEFT JOIN conditions filter some results showing up on the right but again all t1 is returned. But this time the WHERE conditions may filter some records of t1 away.
在 SQL 2 中，LEFT JOIN 条件过滤了一些显示在右侧的结果，但再次返回所有 t1。但是这次 WHERE 条件可能会过滤掉 t1 的一些记录。
INSERT INTO `t1` (`f1`,`f2`,`f3`) VALUES (1,1,1);
INSERT INTO `t2` (`f3`) VALUES (1);
INSERT INTO `t1` (`f1`,`f2`,`f3`) VALUES (1,1,1);
INSERT INTO `t2` (`f3`) VALUES (1);
Since they point to different logic the query must be written based on that and it gives us great power and flexibility.
由于它们指向不同的逻辑，因此必须基于此编写查询，这为我们提供了强大的功能和灵活性。
An INNER JOIN however returns the same result so yes check the optimiser.
然而，内部联接返回相同的结果，所以是检查优化器。
回答by Lukas Eder
It is important to understand the logical order of SQL operationswhen thinking about SQL syntax. JOINis an operator (and ONbelongs to the relevant JOIN) in the FROMclause. The FROMclause is the first operation to be executed logically(optimisers can still choose to reorder things).
在考虑 SQL 语法时，了解SQL 操作的逻辑顺序很重要。JOIN是子句中的运算符（并且ON属于相关的JOIN）FROM。该FROM子句是第一个逻辑执行的操作（优化器仍然可以选择重新排序）。
In your example, there isn't really a difference, but it is easy to construct one, as I've shown in this blog post about the difference between ONand WHEREin OUTER JOIN(the example from the blog post uses the Sakila database):
在您的示例中，实际上并没有什么区别，但是很容易构建一个，正如我在这篇博客文章中展示的关于ON和WHEREin之间的区别OUTER JOIN（博客文章中的示例使用Sakila 数据库）：
First query
第一次查询
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE fa.film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;
Yields:
产量：
ACTOR_ID FIRST_NAME LAST_NAME COUNT
--------------------------------------
194 MERYL ALLEN 1
198 MARY KEITEL 1
30 SANDRA PECK 1
85 MINNIE ZELLWEGER 1
123 JULIANNE DENCH 1
Because we filtered the outer joined table in the WHEREclause, the LEFT JOINwas effectively turned into an INNER JOIN. Why? Because if we had an actor that didn't play in a film, that actor's only row would have fa.film_id IS NULL, and the fa.film_id < 10predicate would thus yield NULL. Such actors are excluded from the result, just as with an INNER JOIN.
因为我们在WHERE子句中过滤了外部连接表，所以LEFT JOIN实际上变成了INNER JOIN. 为什么？因为如果我们有一个没有在电影中出演的演员，那么该演员的唯一行会有fa.film_id IS NULL，fa.film_id < 10因此谓词会产生NULL。这样的演员被排除在结果之外，就像一个INNER JOIN.
Second query
第二次查询
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
AND fa.film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;
Yields:
产量：
ACTOR_ID FIRST_NAME LAST_NAME COUNT
-----------------------------------------
3 ED CHASE 0
4 JENNIFER DAVIS 0
5 JOHNNY LOLLOBRIGIDA 0
6 BETTE NICHOLSON 0
...
1 PENELOPE GUINESS 1
200 THORA TEMPLE 1
2 NICK WAHLBERG 1
198 MARY KEITEL 1
Now, the actors without films are included in the result, because the fa.film_id < 10predicate is part of the LEFT JOIN's ONpredicate
现在，没有电影的演员被包含在结果中，因为fa.film_id < 10谓词是LEFT JOIN'sON谓词的一部分
Conclusion
结论
Always place predicates where they make most sense logically.
始终将谓词放在逻辑上最有意义的地方。
- Are they part of your
JOINoperation? Place them inON - Are they filters on your entire
JOINproduct? Place them inWHERE
- 它们是您
JOIN运营的一部分吗？将它们放入ON - 它们是否对您的整个
JOIN产品进行过滤？将它们放入WHERE
回答by Somnath Muluk
The relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN, so even INNER JOIN queries with WHERE clauses can have the predicates rearrranged by the optimizer so that they may already be excluded during the JOIN process.
关系代数允许 WHERE 子句和 INNER JOIN 中的谓词互换，因此即使是带有 WHERE 子句的 INNER JOIN 查询也可以让优化器重新排列谓词，以便它们可能已经在 JOIN 过程中被排除在外。
I recommend you write the queries in the most readble way possible.
我建议您以最易读的方式编写查询。
Sometimes this includes making the INNER JOIN relatively "incomplete" and putting some of the criteria in the WHERE simply to make the lists of filtering criteria more easily maintainable.
有时，这包括使 INNER JOIN 相对“不完整”，并将某些条件放在 WHERE 中只是为了使过滤条件列表更易于维护。
You can get more from this link: http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause
您可以从此链接获得更多信息：http: //ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause
For example, instead of:
例如，而不是：
SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
ON ca.CustomerID = c.CustomerID
AND c.State = 'NY'
INNER JOIN Accounts a
ON ca.AccountID = a.AccountID
AND a.Status = 1
Write:
写：
SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
AND a.Status = 1
But it depends, of course.
但这当然取决于。
回答by Somnath Muluk
1)
1)
SQL1: select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 **and** t1.f2=1 and t1.f3=0
In this, parser will check each row of t1 with each row of t2 with these 3 conditions. Getting faster result.
在这种情况下，解析器将使用这 3 个条件检查 t1 的每一行和 t2 的每一行。获得更快的结果。
2) SQL2: select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 **where** t1.f2=1 and t1.f3=0
2) SQL2: select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 **where** t1.f2=1 and t1.f3=0
In this, join only take 1st condition and then the result got from join is filtered with those 2 conditions. And will take more time than 1st query.
在这种情况下，join 仅采用第一个条件，然后使用这两个条件过滤从 join 获得的结果。并且会比第一次查询花费更多的时间。
You can get more from this link: http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause
您可以从此链接获得更多信息：http: //ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause
