left join on 后跟and和where区别

       相信很多朋友在写sql的时候会用到 left join on，right join on， inner join on 这种连接方式，但是如果想在我们连接的表中去过滤某个条件该怎么做呢，是直接在 on 后面加 and 还是直接在where中加条件呢？之前自己遇到这种情况总是先去做尝试，对于这其中的原理不明白。通过网上搜索以及自己实践，把自己的见解以及网上搜到的一些结果写下来，以便以后回顾使用。

 

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

left join on ：

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 on 后面跟and条件，先会在右边表中对and条件进行过滤，然后再跟左边主表进行关联。

2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

 

示例：

表table1

id    no

1     101

2     102

3     103

4     104

5     105

 

表table2

no     name

101   a

103   b

104   c

 

SELECT * FROM table1 t1  LEFT JOIN table2 t2  ON (t1.no = t2.no AND t2.no='103')

查询结果如下：

t1.id    t1.no    t2.no    t2.name

1         101     (null)     (null) 

2         102     (null)     (null) 

3         103     103       b

4         104     (null)     (null) 

5         105     (null)     (null)

 

SELECT * FROM table1 t1  LEFT JOIN table2 t2  ON t1.no = t2.no WHERE t2.no='103'

查询结果如下：

t1.id    t1.no    t2.no    t2.name

3         103     103       b

 

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。

而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。