join 条件在on和where 后的区别

首先建两个表来测试下。

create table a(id number,type number );

insert into a values(1,1);

insert into a values(2,1);

insert into a values(3,2);

insert into a values(4,1);

insert into a values(5,2);

create table b(id number);

insert into b values(1);

insert into b values(2);

insert into b values(5);

 

            表a                    表b

id              type              id

1                1                   1

2                1                   2 

3                2

4                1

5                2                    5

 

内连接时

select * from a inner join b on a.id =b.id where a.type = 1;

select * from a inner join b on a.id =b.id and a.type = 1;

结果一样

 

左连接时

select * from a left join b on a.id =b.id where a.type = 1;

 

select * from a left join b on a.id =b.id and a.type = 1;

 

可以发现当a.type = 1条件在on 后面时， 和where时是不同的

 

可以发现当a.type = 1条件在on 后面时， 和where时是不同的

 

分析：

内连接：返回符合条件的行。

 

左外连接：以左边表为基础，返回右边表中符合条件的行。

也就是左表的全部记录都在。所有筛选条件都是针对右表

当条件在on后时

所以上面左连接时，

a表id 为1，在b表中也有id = 1，且a表这行的记录type = 1，条件为真，所以 和b有关的列有值。

a表id 为2，在b表中也有id = 2，且a表这行的记录type = 1，条件为真，所以 和b有关的列有值。

a表id 为3，在b表中没有id = 3，条件为假，所以 和b有关的列值为空。

a表id 为4，在b表中没有id = 4 ，条件为假 ，所以 和b有关的列 值为空 。

a表id 为5，在b表中也有id = 5，且a表这行的记录type = 2，条件为假，所以 和b有关的列 值为空 。

所以最后的结果如图2所示

 

当条件在where后时

a表id 为1，在b表中也有id = 1，条件为真，所以 和b有关的列有值。

a表id 为2，在b表中也有id = 2，条件为真，所以 和b有关的列有值。

a表id 为3，在b表中没有id = 3，条件为假，所以 和b有关的列值为空。

a表id 为4，在b表中没有id = 4 ，条件为假 ，所以 和b有关的列 值为空 。

a表id 为5，在b表中也有id = 5，条件为 真 ，所以 和b有关的列 有值  。

结果为：

 

然后根据a.type = 1来筛选条件，所以最后结果为图1

 

 

右连接时

select * from a right join b on a.id =b.id where a.type = 1;

 

select * from a right join b on a.id =b.id and a.type = 1;

 

 

右外连接：以左边表为基础，返回右边表中符合条件的行。

同样，也就是右表的全部记录都在。所有筛选条件都是针对左表

所以上面右连接时，

b表id 为1，在a表中也有id = 1，且a表这行的记录type = 1，条件为真，所以 和a有关的列有值。

b表id 为2，在a表中也有id = 2，且a表这行的记录type = 1，条件为真，所以 和a有关的列有值。

b表id 为5，在a表中没有id = 5，且a表这行的记录type = 2，条件为假，所以 和a有关的列值为空。

所以最后的结果如图4所示

 

b表id 为1，在a表中也有id = 1 ，条件为真，所以 和a有关的列有值。

b表id 为2，在a表中也有id = 2，条件为真，所以 和a有关的列有值。

b表id 为5，在a表中没有id = 5，条件为真，所以 和a有关的列 有值 。

结果为:

 

然后根据a.type = 1来筛选条件，所以最后结果为图3

结论：当内连接时，条件放在where后和on后结果一样，

           当外连接时，条件放在where后和on后结果不一样，根据自己的需要选择到底放在哪。