Mysql内连接、外连接、全连接

前言

用两个表（a_table、b_table），关联字段a_table.a_id和b_table.b_id来演示一下MySQL的内连接、外连接（左(外)连接、右(外)连接、全(外)连接）。

MySQL版本：Server version: 5.6.31 MySQL Community Server (GPL)

数据库表：a_table、b_table

主题：内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）、交叉连接

前提

建表语句：

CREATE TABLE `a_table` (
  `a_id` int(11) DEFAULT NULL,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `b_table` (
  `b_id` int(11) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表测试数据：

mysql1

内连接(等值连接)

关键字：inner join on

语句：

1	select * from a_table a inner join b_table b on a.a_id = b.b_id

执行结果：

mysql2

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

mysql3

左连接

关键字：left join on / left outer join on

语句：

1	select * from a_table a left join b_table b on a.a_id = b.b_id

执行结果：

mysql4

说明：

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录，并且右表记录不足的地方均为NULL。

mysql5

右连接

关键字：right join on / right outer join on

语句：

1	select * from a_table a right outer join b_table b on a.a_id = b.b_id

执行结果：

mysql6

说明：

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。

与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来，并且左表记录不足的地方均为NULL。

mysql7

全连接

MySql不支持全连接查询，可以用union代替.

为做全连接演示，所以在Oracle新建两张：

表message1：

mysql9

表message2：

mysql10

关键字：FULL JOIN

语句：

1
2
3

select * from message1 a
FULL JOIN message2 b
on a.id=b.id

查询结果如图所示：

mysql11

全连接的图片效果：

mysql8

总结：Full outer join展示的是a表和b表的全部信息(a和b的并集)。但需要注意的是，对于没有匹配的记录(即a.id和b.id没有一一对应的)，则会以null做为值。可以使用IF NULL判断。

交叉连接(笛卡尔积)

不带where条件子句，它返回的是被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。

例如上表中：message1表的3条数据*message2表的4条数据，最后得到12条数据，就是下面笛卡尔积查询出来的总记录数。

查询语句：
1
2
select * from message1 a
cross JOIN message2 b
查询结果如图所示：
如果带where，返回或显示的是匹配条件成立的行数。

查询语句：
1
2
3
4
select * from message1 a
cross JOIN message2 b
where
a.id=b.id
查询结果如图所示：

总结：
1.CROSS JOIN(笛卡尔积)是两个表每一个字段相互匹配，得出的结果就是笛卡尔积。笛卡尔积也等同于交叉连接。

2.CROSS JOIN(笛卡尔积)带条件查询，查询结果跟等值连接(也叫内连接)、where连接的查询结果是一样，并且后面加条件只能用where，不能用on。