SQL---SQL左右连接中的on and和on where的区别

左连接

先上表结构以及数据：

情况一:on 后面跟and

and筛左表:   执行sql:SELECT * FROM a left join b ON a.id=b.id and a.type = 1;

因为左连接不管on and语句是否为真都必须返回左表所有的记录，所以and A.type=1筛左表没有起到任何作用。

and筛右表:   执行sql: SELECT * FROM a left join b ON a.id=b.id and b.class = 1;

左连接只保证左表不能被筛选,右表还是可以被and筛选的。

情况二:on 后面跟 where 

执行sql:SELECT * FROM a left join b ON a.id=b.id WHERE a.type = 1;

上面的sql是分两步走的

第一步:先on, 两表联查生成一个临时表，也就是可以理解为就是一个左连接：select * from A left join B on A.id = B.id;

第二步:再where A.type = 1对临时表进行过滤，筛选出A.type=1的，显然结果正确。

---------------------------------------------------------------------------------------------------------------------------------

右连接

由于刚开始表的数据不是太适合，所以先稍微更新一下，这样更好观察inner join和left join在and和where的不同之处。

 

情况一:on 后面跟 and

不加and :执行sql: SELECT * FROM a join b on a.id=b.id ;

加and筛选左表 : 执行sql: SELECT * FROM a join b on  a.id = b.id and a.type = 1;

加and筛选右表 : 执行sql: SELECT * FROM a join b on  a.id = b.id and b.class= 1;

显然输出结果与左连接的不一样，证实了在内连接中不管是对左表还是右表进行筛选，on and都会对生成的临时表进行过滤

情况二:on 后面跟 where

 不加where :执行sql: SELECT * FROM a join b on a.id=b.id ;

加where :执行sql: SELECT * FROM a join b on a.id=b.id WHERE b.class = 2;

where对生成临时表后的结果筛选成功. 

总结：

        在使用left join时，on和where条件的区别如下：  
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的所有记录。（实际上左连接中如果and语句是对左表进行过滤的，那么不管真假都不起任何作用。如果是对右表过滤的，那么左表所有记录都返回，右表筛选以后再与左表连接返回）  
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。

        在使用inner join时，不管是对左表还是右表进行筛选，on and和on where都会对生成的临时表进行过滤。