将条件放入内部联接的ON子句与主查询的where子句之间在逻辑上有区别吗? [英] Is there a logical difference between putting a condition in the ON clause of an inner join versus the where clause of the main query?

问题描述

考虑这两个类似的SQL

Consider these two similar SQLs

(ON子句中的条件)

select t1.field1, t2.field1
from
table1 t1 inner join table2 t2 on t1.id = t2.id and t1.boolfield = 1

(WHERE子句中的条件)

(condition in WHERE clause)

select t1.field1, t2.field1
from
table1 t1 inner join table2 t2 on t1.id = t2.id
where t1.boolfield = 1

我已经对此进行了一些测试，我可以看到在两个不同的地方为外部联接放置一个条件之间的区别. 但是在内部联接的情况下，结果集会有所不同吗?

I have tested this out a bit and I can see the difference between putting a condition in the two different places for an outer join. But in the case of an inner join can the result sets ever be different?

推荐答案

对于INNER JOIN，没有有效的区别，尽管我认为第二个选择更干净.

For INNER JOIN, there is no effective difference, although I think the second option is cleaner.

对于LEFT JOIN，有很大的不同. ON子句指定将从表中选择要比较的记录，WHERE子句过滤结果.

For LEFT JOIN, there is a huge difference. The ON clause specifies which records will be selected from the tables for comparison and the WHERE clause filters the results.

示例1:返回tbl 1中的所有行，并将它们与tbl2中boolfield = 1的适当行进行匹配

Example 1: returns all the rows from tbl 1 and matches them up with appropriate rows from tbl2 that have boolfield=1

Select *
From tbl1
  LEFT JOIN tbl2 on tbl1.id=tbl2.id and tbl2.boolfield=1

示例2:将仅包含tbl1中的行，其中tbl2中具有匹配行且boolfield = 1的行.它会联接表，然后过滤掉不符合条件的行.

Example 2: will only include rows from tbl1 that have a matching row in tbl2 with boolfield=1. It joins the tables, and then filters out the rows that don't meet the condition.

Select *
From tbl1
  LEFT JOIN tbl2 on tbl1.id=tbl2.id
WHERE tbl2.boolfield=1

这篇关于将条件放入内部联接的ON子句与主查询的where子句之间在逻辑上有区别吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！