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

简介

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

SELECT

FROM [连接类型]

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