结论
一、在使用inner join时,同时使用on和where条件,结果没有区别,前者是先求笛卡尔积然后按照on后面的条件进行过滤,后者是先用on后面的条件过滤,再用where的条件过滤。
二、在使用left jion(right jion同理)时,on和where条件的区别如下:
1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
例子
class表
student表
执行inner join操作:
1、
SELECT * FROM class c INNER JOIN student s ON c.id=s.id AND c.id!=2 AND s.id!=2;
结果如下:
2、
SELECT * FROM class c INNER JOIN student s ON c.id=s.id WHERE c.id!=2 AND s.id!=2;
结果如下:
从上面的例子来看子来看,inner join时,on 和 where条件对结果没有区别,前者是先求笛卡尔积然后按照on后面的条件进行过滤,后者是先用on后面的条件过滤,再用where的条件过滤。
执行left join操作:
1、
SELECT * FROM class c LEFT JOIN student s ON c.id=s.id AND c.id!=2 AND s.id!=2;
结果如下:
出现了不一样的地方,可以看到id为10和2的记录还在,这是由on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。使得left join时on后面的条件只对右表有效,因此,我们看到左表的记录还存但是右表的id=4的记录没了。
2、
SELECT * FROM class c LEFT JOIN student s ON c.id=s.id WHERE c.id!=2 AND s.id!=2;
结果如下:
从上述例子中可以看到,where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
同理,right join的原理是一样的。