表左连接时 where 子句和 on 子句有什么区别? [英] What's the difference between where clause and on clause when table left join?

问题描述

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:

select t1.f1,t2.f2 
from t1 
  left join t2 on t1.f1 = t2.f2 
where t1.f2=1 and t1.f3=0

区别在于 where 和 on 子句，返回结果是否相同?有什么区别?DBMS 是否以相同的方式运行它们?谢谢.

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 clause 仅适用于有问题的连接.

The where clause applies to the whole resultset; the on clause only applies to the join in question.

在提供的示例中，所有附加条件都与连接内侧的字段相关 - 因此在本示例中，两个查询实际上是相同的.

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 outer side of the join, it would have made a significant difference.

您可以从此链接获得更多信息:http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause

You can get more from this link: http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause

例如:

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

- 做不同的事情- 前者将离开连接到 f4 为 1 的 t2 记录，而后者已有效地转回到 t2 的内部连接.

- 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.

这篇关于表左连接时 where 子句和 on 子句有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！