mysql 全外连接_【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中的全外连接就能实现啦！