SQL学习精粹之内外连接以及where和on条件的区别-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

sql内外连接（重点）

在oracle的SQL语句常用的连接有内连接（inner join），外连接（outer join）等，内连接又包括等值连接，非等值连接，自连接；而外连接又分为左连接和右连接全连接。其中默认的是内连接的等值连接。

为了方便我们创建两张最简易的表A、B，具体的表结构参看下面，来分析内连接与外连接的区别

图1，表A 图2 表B

图1 图2

两个表要做连接，必须要有连接字段，而在表A和表B中连接字段是Aid和Bnamid,下图说明了连接之间关系图3

图3

（1) 内连接：利用内连接（等值）就可获取蓝色的公共部分C，即图3中的数据集C，结果集为如下：

图4

   其实select * from A join B on A.Aid=B.Bnamid;等价于select * from A,B where A.Aid=B.Bnamid;
       注：非等值连接主要的话是针对一个范围来查询数据，自连接主要就是把1张表看成两张表来用
   (2)外连接：分为左外连接（left join）与右外连接（right join）

      左外连接即公共显示的蓝色部分C1+显示黄色的记录集A1，显示语句等价于

select * from A,B where A.Aid=B.Bnamid(+)；

注：sql中(+),表示外连接可以省略 left/right join 。。and。。等关键字，其中(+)在等号左边表示右外连接，在等号右边表示左外连接。

图 5

右外连接即公共显示的蓝色部分C1+显示绿色的B1，显示语句等价于

select * from A,B where A.Aid(+)=B.Bnamid；

图6

表A和表B情况是相对的，以上实验都是A在左边的情况，其实A left join B与B right join A的情况的结果集是一样的。

另外，全连接 full join表示三个部分全部包含到结果集，彼此没有与之匹配的数据行填充null。

连接条件子语句where和on的区别

测试表环境：

drop table if exists join_a;
create table  join_a(
f1 int not null comment '字段1',
f2 int not null comment '字段2'
)engine=innodb default charset=utf8 comment '连接测试A表';

insert into join_a(f1,f2) value(1,1);
insert into join_a(f1,f2) value(1,1);
insert into join_a(f1,f2) value(1,2);
insert into join_a(f1,f2) value(1,2);
insert into join_a(f1,f2) value(2,3);
insert into join_a(f1,f2) value(2,3);
insert into join_a(f1,f2) value(2,4);
insert into join_a(f1,f2) value(2,4);

drop table if exists join_b;
create table  join_b(
f1 int not null comment '字段1',
f2 int not null comment '字段2'
)engine=innodb default charset=utf8 comment '连接测试B表';

insert into join_b(f1,f2) value(1,5);
insert into join_b(f1,f2) value(1,5);
insert into join_b(f1,f2) value(1,6);
insert into join_b(f1,f2) value(1,6);
insert into join_b(f1,f2) value(2,7);
insert into join_b(f1,f2) value(2,7);
insert into join_b(f1,f2) value(2,8);
insert into join_b(f1,f2) value(2,8);

左外连接

左外连接（无条件）：

select t1.* ,t2.* from join_a t1 left join join_b t2 on t1.f1=t2.f1;

运行结果：

+----+----+----+----+
| f1 | f2 | f1 | f2 |
+----+----+----+----+
|  1 |  1 |  1 |  5 |
|  1 |  1 |  1 |  5 |
|  1 |  2 |  1 |  5 |
|  1 |  2 |  1 |  5 |
|  1 |  1 |  1 |  5 |
|  1 |  1 |  1 |  5 |
|  1 |  2 |  1 |  5 |
|  1 |  2 |  1 |  5 |
|  1 |  1 |  1 |  6 |
|  1 |  1 |  1 |  6 |
|  1 |  2 |  1 |  6 |
|  1 |  2 |  1 |  6 |
|  1 |  1 |  1 |  6 |
|  1 |  1 |  1 |  6 |
|  1 |  2 |  1 |  6 |
|  1 |  2 |  1 |  6 |
|  2 |  3 |  2 |  7 |
|  2 |  3 |  2 |  7 |
|  2 |  4 |  2 |  7 |
|  2 |  4 |  2 |  7 |
|  2 |  3 |  2 |  7 |
|  2 |  3 |  2 |  7 |
|  2 |  4 |  2 |  7 |
|  2 |  4 |  2 |  7 |
|  2 |  3 |  2 |  8 |
|  2 |  3 |  2 |  8 |
|  2 |  4 |  2 |  8 |
|  2 |  4 |  2 |  8 |
|  2 |  3 |  2 |  8 |
|  2 |  3 |  2 |  8 |
|  2 |  4 |  2 |  8 |
|  2 |  4 |  2 |  8 |
+----+----+----+----+
32 rows in set

说明：取t1表的第一行,扫瞄t2表,按条件做对比,如果满足条件,就加入返回结果表.
然后取t1表的第二行,扫瞄t2表,按条件做对比,如果满足条件,就加入返回结果表.

。。。。。。。。。。。。。
重复以上过程,直到t1表扫描结束。

左表条件（on+and条件）：

select t1.* ,t2.* from join_a t1 left join join_b t2 on t1.f1=t2.f1 and t1.f2=1;

说明：给左表加条件的时候,左表满足条件的,按上面的过程返回值,左表不满足条件的,直接输出,右表的列补null。

以下三种语句查询结果一样：

--左外连接（where条件）,不完整的数据不显示
select t1.* ,t2.* from join_a t1 left join join_b t2 on t1.f1=t2.f1 where t1.f2=1;
--内连接（on+and条件）,不完整的数据不显示
select t1.* ,t2.* from join_a t1 inner join join_b t2 on t1.f1=t2.f1 and t1.f2=1;
--使用where条件来连接表(and条件),不完整的数据不显示
select t1.* ,t2.* from join_a t1 , join_b t2 where t1.f1=t2.f1 and t1.f2=1;

--其中后两种效率更高

说明：如果有where子语句，先执行where后连接查询，执行where的时候已经把不满足条件的直接去除，所以最后的结果没有填充null的行。效果同inner join。

当外连接使用where条件语句的时候，可以把left join（或者right join）改为inner jin, 因为inner join比left join 要快!

右表条件（on+and条件）：

select t1.* ,t2.* from join_a t1 left join join_b t2 on t1.f1=t2.f1 and t2.f2=5;

以下三种语句查询结果一样：

--on+where,不完整的数据不显示
select t1.* ,t2.* from join_a t1 left join join_b t2 on t1.f1=t2.f1 where t2.f2=5;
--inner+on+and,不完整的数据不显示
select t1.* ,t2.* from join_a t1 inner join join_b t2 on t1.f1=t2.f1 and t2.f2=5;
--where+and,不完整的数据不显示
select t1.* ,t2.* from join_a t1 , join_b t2 where t1.f1=t2.f1 and t2.f2=5;

--其中后两种效率更高

转载于:https://my.oschina.net/iyinghui/blog/778866