join 中的on和where的区别

目录

前言：

区别：

举例：

---

前言：

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

区别：

在使用left join时，on和where条件的区别如下：

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的数据，右边表不符合条件的话就显示为空(这个是left join，right join 则相反)。
2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义了，条件不为真的就全部过滤掉。

举例：

如下：两张表 t_number和t_age

t_number表

t_age  

我们先笛卡尔积一下

SELECT a.id,a.name,b.age FROM t_number a ,t_age b;

SELECT a.id,a.name,b.age FROM t_number a JOIN t_age b;  

上面这两个查询结果是一样的

join on过滤

SELECT a.id,a.name,b.age FROM t_number a JOIN t_age b ON a.id=b.id; 

这样的话，就相当于对笛卡尔积进行过滤，过滤出两个表主键相同的数据

where过滤

SELECT a.id,a.name,b.age FROM t_number a JOIN t_age b where a.id=b.id;

和上面一样的，因为相当于都是inner 查询，都是笛卡尔积后进行过滤

中间表都是这个

left join

看一下left join，对比一下中间表，因为on的条件是id相同，t_age表内没有id为2和4的数据，但是t_number的数据还是要全部显示，t_number的数据在t_age表内没有数据的话，就显示为空

SELECT a.id,a.name,b.age FROM t_number a left JOIN t_age b ON a.id=b.id;

SELECT a.id,a.name,b.age FROM t_number a left JOIN t_age b ON a.id=b.id and b.age>20;

再看一下这个，再上面的基础上加上age>20，那样的话，苹果那个也不符合条件，但是苹果在number表内，left join苹果还是要显示的，age>20，苹果对应的主键id在age表中就不符合条件了，所以age表中的数据显示为空

上面的查询这样弄，如果换成where呢

SELECT a.id,a.name,b.age FROM t_number a left JOIN t_age b ON a.id=b.id where b.age>20;

就只显示香蕉的这个数据了，因为是最后根据结果集去过滤的条件