内连接 外连接 交叉连接总结

本篇文章总结了一下SQL中的五种连接-内连接、左外连接、右外连接、全连接、交叉连接

一、内连接 (full join/full outer join)

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

二、外连接(full join/full outer join)

返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。其中： 

1、左连接：左向外连接的结果集包括LEFT  OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

2、右连接：右向外连接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

3、全连接：完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   注：Mysql不支持全连接，Oracle、DB2、MSSQL都支持全连接

三、交叉连接(full join/full outer join)
交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，teacher表中有5位老师，而student表中有9个学生，则下列交叉连接检索到的记录数将等于5*9=45行。

下面举例说明：
创建表：
create table teacher(
t_id int,
t_Name varchar(30)
)character set 'utf8';

create table student(
s_id int,
s_Name varchar(30)
)character set 'utf8';

create table tea_stu_mapping(
t_id int,
s_id int

);

向表中插入相应数据
insert into teacher values(1,'张梦男');
insert into teacher values(2,'王欣');
insert into teacher values(3,'叶凡');
insert into teacher values(4,'许敏');
insert into teacher values(5,'张子良');

insert into student values(1,'张丹');
insert into student values(2,'李娜');
insert into student values(3,'林金');
insert into student values(4,'张欣然');
insert into student values(5,'谢鑫');
insert into student values(6,'董鑫');
insert into student values(7,'李强');
insert into student values(8,'张自在');
insert into student values(9,'石磊');

insert into tea_stu_mapping values(1,1);
insert into tea_stu_mapping values(1,2);
insert into tea_stu_mapping values(2,3);
insert into tea_stu_mapping values(2,4);
insert into tea_stu_mapping values(3,5);
insert into tea_stu_mapping values(3,6);
insert into tea_stu_mapping values(4,7);
insert into tea_stu_mapping values(4,8);

--内连接 inner join
select t.t_id, t.t_Name, s.s_id, s.s_Name from teacher t inner join tea_stu_mapping map on t.t_id = map.t_id inner join student s on s.s_id = map.s_id order by t.t_id, s.s_id;


--左连接(左外连接) left join(left outer join)
select t.t_id, t.t_Name, s.s_id, s.s_Name from teacher t left join tea_stu_mapping map on t.t_id = map.t_id left join student s on s.s_id = map.s_id order by t.t_id, s.s_id;


--右连接(右外连接) right join(right outer join)
select t.t_id, t.t_Name, s.s_id, s.s_Name from teacher t right join tea_stu_mapping map on t.t_id = map.t_id right join student s on s.s_id = map.s_id order by t.t_id, s.s_id;


--全连接(全外连接) full join(full outer join)
select t.t_id, t.t_Name, s.s_id, s.s_Name from teacher t full join tea_stu_mapping map on t.t_id = map.t_id full join student s on s.s_id = map.s_id;
因为Mysql不支持全连接full join，所以这里要在Mysql中实现全连接的效果使用的是"左连接+union+右连接"的方式。
下面就是在Mysql实现全连接的效果sql
select t.t_id, t.t_Name, s.s_id, s.s_Name from teacher t left join tea_stu_mapping map on t.t_id = map.t_id left join student s on s.s_id = map.s_id
union
select t.t_id, t.t_Name, s.s_id, s.s_Name from teacher t right join tea_stu_mapping map on t.t_id = map.t_id right join student s on s.s_id = map.s_id;

与union相关的还有union all,下面在看看union all的与union的差别

select t.t_id, t.t_Name, s.s_id, s.s_Name from teacher t left join tea_stu_mapping map on t.t_id = map.t_id left join student s on s.s_id = map.s_id
union all
select t.t_id, t.t_Name, s.s_id, s.s_Name from teacher t right join tea_stu_mapping map on t.t_id = map.t_id right join student s on s.s_id = map.s_id;

Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All，两个要联合的SQL语句 字段个数必须一样，而且字段类型要"相容"（一致）。

--交叉连接cross join

select * from teacher cross join student;

这种记录便为两个表的记录的笛卡尔积。