mysql连接总结及实例演示

mysql表数据

student

grade

grade_copy
(班级字段名添加一列student表一致的名字，自然连接演示用)

一、交叉连接(CROSS JOIN)

也称作笛卡尔积。返回左表中的所有行，左表中的每一行与右表中的所有行组合。 内连接，外连接实际上都是在笛卡尔积的基础上对记录进行筛选。与内连接的区别是：交叉连接不跟条件。mysql中用on仍然可以正常获得需要的数据结果表，这是MySQL独有的容错写法，有join condition时cross会被忽略。

Select …… from 表A cross join 表 B

select * from student  cross join grade 

二、内连接(INNER JOIN)

使用on或者where加查询条件，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。
按照匹配的条件一般可以分为自然连接、等值连接和不等值连接。

Select …… from 表A , 表 B where 表A.m=表B.n
Select …… from 表A inner join 表 B on 表A.m=表B.n

1、等值连接

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

select * from student as a,grade as b where a.class = b.classid

select * from student as a inner join grade as b on a.class = b.classid

2、非等值连接

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

select * from student inner join grade on class<3

3、自然连接（natural join）

等值连接的一种，自动将关联表中的相同字段进行比较，故无须自己添加连接条件。在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。注意：后面再加where判定条件时（不能用on），不要用表名.字段名的形式。natural join 默认是同名字段完全匹配的 inner join。

Select …… from 表A natural join 表B

select * from student natural join grade_copy

*此处演示用的事student表和grade_copy表（根据两表中共有的class关键字进行连接）

三、外连接(OUTER JOIN)

保留所有匹配的记录，包括部分不匹配的记录。
使用on加查询条件。
分为左外连接，右外连接，全外连接。

1、左外连接(left outer join / left join)

除了保存所有匹配的数据记录，还保留左表中未匹配的数据记录。或者说查询出的结果除了两表都匹配的记录外，还会查询出左表的其余记录，同时右表对应记录填充null。

Select …… from 表A left [outer] join 表B on 表A.m=表B.n

select * from student [as] a left [outer] join grade [as] b on a.class = b.classid

2、右外连接(right outer join / right join)

除了保存所有匹配的数据记录，还保留右表中未匹配的数据记录。或者说查询出的结果除了两表都匹配的记录外，还会查询出右表的其余记录，同时左表对应记录填充null。

Select …… from 表A rignt [outer] join 表B on 表A.m=表B.n

select * from student [as] a right [outer] join grade [as] b on a.class = b.classid

3、全外连接/全连接 (full outer join / full join)

除了保存所有匹配的数据记录，还保留左表和右表中匹配的数据记录。没有WHERE 子句，返回左表和右表所有数据。当某行在另一个表中没有匹配行时，则另一个表的选择列表列填充null。
MySQL并不支持，所以只能采用关键字 UNION 来联合左，右连接的方法。

select …… from 表A left [outer] join 表B on 表A.m=表B.n
union
select …… from 表A right [outer] join 表B on 表A.m=表B.n

select * from student a right join grade  b on a.class = b.classid
union
select * from student a left join grade  b on a.class = b.classid

四、自连接

自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。
mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要用as语句为两个表分别取不同的名字以区分。

select …… from 表A [as] A1，表A [as] A2 where 表A1.m=表A2.n

select * from student [as] a1,student [as] a2 where a1.stuid=a2.project order by a1.project

（排序是为了看的更清楚，更好理解）

select a1.pname [as] '学科',a2.pname [as] '专业' from student [as] a1,student [as] a2 where a1.stuid=a2.project order by a1.project

（更清晰的看到学科与专业的联系）