【MySQL】中的多表连接是什么，以及如何实现全外连接查询？

简介

在MySQL中，多表连接的语法格式如下：

SELECT <查询列表>
FROM <表名1> [连接类型] 
JOIN <表名2> ON <连接条件>
WHERE <查询条件> 

其中连接类型有三种：

• 内连接（inner join）

• 外连接（outer join）

• 交叉连接（cross join）

多表连接往往要通过表之间的物理外键（或者逻辑外键）进行关联查询。

接下来就分别介绍一下这几个连接的具体用法和区别。

内连接

在数据库多表关联查询中，内连接是最常用的连接类型了，当然也是默认的连接类型。

可以在FROM子句中使用INNER JOIN（INNER关键字可以省略）来实现内连接。

示例：
现在有两张表：订单信息表（orders）和订单详情表（orderdetails），它们的关系如下图所示。

部分数据如下：

订单信息表(orders)

orderNumber	orderDate	shippedDate	status	customerNumber
10100	2013-01-06	2013-01-10	Shipped	363
10101	2013-01-09	2013-01-11	Shipped	128
10102	2013-01-10	2013-01-14	Shipped	181
10103	2013-01-29	2013-02-02	Shipped	121
10104	2013-01-31	2013-02-01	Shipped	141

订单详情表（orderdetails）

orderNumber	productCode	quantityOrdered	priceEach
10100	S18_1749	30	136.00
10100	S18_2248	50	55.09
10100	S18_440	22	75.46
10100	S24_3969	49	35.29
10101	S18_2325	25	108.06

那么如何根据这两张表关联查询出订单号（orderNumber）, 客户编号（customerNumber）, 下单日期（orderDate）, 订单状态（status）, 产品编号（productCode）, 下单数量（quantityOrdered）, 单价（priceEach）呢？

答案：

SELECT o.orderNumber, o.customerNumber, o.orderDate, o.`status`, od.productCode, od.quantityOrdered, od.priceEach
FROM orders o 
JOIN orderdetails od ON od.orderNumber = o.orderNumber;

查询结果：

orderNumber	customerNumber	orderDate	status	productCode	quantityOrdered	priceEach
10100	363	2013-01-06	Shipped	S18_1749	30	136.00
10100	363	2013-01-06	Shipped	S18_2248	50	55.09
10100	363	2013-01-06	Shipped	S18_4409	22	75.46
10100	363	2013-01-06	Shipped	S24_3969	49	35.29
10101	128	2013-01-09	Shipped	S18_2325	25	108.06

外连接

使用外连接时，以主表中每行的数据去匹配从表中的数据行，如果符合连接条件则返回到结果集中；如果没有找到匹配行，则主表的行仍然保留，并且返回到结果集中，相应的从表中的数据行被填上NULL值后也返回到结果集中。

外连接有3种类型，分别是：

• 左外连接（LEFT OUTER JOIN）
• 右外连接（RIGHT OUTER JOIN）
• 全外连接（FULL OUTER JOIN）

但是，在MySQL中，是不支持全外连接的。这一点要注意。

左外连接

左外连接的结果集中包含左表（JOIN关键字左边的表）中所有的记录，如果右表中没有满足连接条件的记录，则结果集中右表中的相应行数据填充为NULL。

示例：现在有两张表员工信息表（employees）和客户信息表（customers），它们的关系如下图所示：

其中部分数据如下：

员工信息表(customers)

employeeNumber	lastName	firstName
1002	Murphy	Diane
1056	Patterson	Mary
1165	Jennings	Leslie
1370	Hernandez	Gerard
1504	Jones	Barry

顾客信息表（customers）

customerNumber	customerName	salesRepEmployeeNumber
119	La Rochelle Gifts	1370
121	Baane Mini Imports	1504
124	Mini Gifts Distributors Ltd.	1165
125	Havel & Zbyszek Co
128	Blauer See Auto, Co.	1504

那么如何根据这两张表统计出每位员工对应的客户，结果需包含员工号（employeeNumber）、姓名（employeeName）、顾客编号（customerNumber）、顾客姓名（customerName）呢？

答案：

SELECT e.employeeNumber, CONCAT(e.firstName, ' ', e.lastName) AS `employeeName`, c.customerNumber, c.customerName
FROM employees e
LEFT JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumber

这里面利用了concat函数来拼接姓名，然后使用分组进行统计。
把employees表作为左表，通过employeeNumber和customers表进行关联。

最终查询结果如下：

employeeNumber	employeeName	customerNumber	customerName
1002	Diane Murphy
1056	Mary Patterson
1165	Leslie Jennings	124	Mini Gifts Distributors Ltd.
1370	Gerard Hernandez	119	La Rochelle Gifts
1504	Barry Jones	121	Baane Mini Imports
1504	Barry Jones	128	Blauer See Auto, Co.

在左外连接时，由于部分员工没有顾客，所以顾客的信息会显示为null。

右外连接

右外连接的结果集中，包含满足连接条件的所有数据，此外还包括右表（JOIN关键字右边的表）中不满足条件的数据，这时左表中的相应行数据为NULL。

示例：

同样以左外连接示例中的两表为例，那么如何根据这两张表统计出每位客户对应的销售员工，结果需包含顾客编号（customerNumber）、顾客姓名（customerName）、员工号（employeeNumber）、姓名（employeeName）呢？

很简单，我们只需要将 LEFT JOIN 改为 RIGHT JOIN 即可。

答案：

SELECT e.employeeNumber, CONCAT(e.firstName, ' ', e.lastName) AS `employeeName`, c.customerNumber, c.customerName
FROM employees e
RIGHT JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumber

查询出来结果如下：

employeeNumber	employeeName	customerNumber	customerName
1370	Gerard Hernandez	119	La Rochelle Gifts
1504	Barry Jones	121	Baane Mini Imports
1165	Leslie Jennings	124	Mini Gifts Distributors Ltd.
		125	Havel & Zbyszek Co
1504	Barry Jones	128	Blauer See Auto, Co.

可以看到，编号是125的顾客是没有对应销售员工的，相应字段数据会显示为null。

那么如何实现全外连接呢？

左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充；也就是：

• 左外连接 = 左表全部记录 + 相关联结果
• 右外连接 = 右表全部记录 + 相关联结果
• 全外连接 = 左表全部记录 + 右表全部记录 + 相关联结果 = 左外连接 + 右外连接 - 相关联结果（即去重复）

那在MySQL中应该怎么做呢？

这就用到了另外一个操作符：UNION，它用于合并两个或多个 SELECT 语句的结果集，一般这样的多个SELECT语句查询出的结果字段都是一样的。默认情况下，UNION操作符是会去除重复数据的。
如果不想去除重复数据，请使用UNION ALL。

同样根据上面的示例，需要查询出所有销售员工和对应的顾客信息，要求顾客有销售员工对应的，要筛选出来，没有对应的销售员工也要把顾客信息查出来，没有顾客的销售员工也查出来。

这时候就需要用到全外连接。

方法是：把左外连接查询和右外连接查询做一个UNION即可。

答案:

SELECT e.employeeNumber, CONCAT(e.firstName, ' ', e.lastName) AS `employeeName`, c.customerNumber, c.customerName
FROM employees e
LEFT JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumber

UNION

SELECT e.employeeNumber, CONCAT(e.firstName, ' ', e.lastName) AS `employeeName`, c.customerNumber, c.customerName
FROM employees e
RIGHT JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumber

查出来的数据是这样的：

employeeNumber	employeeName	customerNumber	customerName
1002	Diane Murphy
1056	Mary Patterson
1165	Leslie Jennings	124	Mini Gifts Distributors Ltd.
1370	Gerard Hernandez	119	La Rochelle Gifts
1504	Barry Jones	121	Baane Mini Imports
1504	Barry Jones	128	Blauer See Auto, Co.
		125	Havel & Zbyszek Co

可以看出来，是左外连接 + 右外连接 两者的并集去重后的结果。

就这样，MySQL中的全外连接就能实现啦！