简介
在MySQL中,多表连接的语法格式如下:
SELECT FROM [连接类型] JOIN ON WHERE
其中连接类型有三种:
内连接(inner join)外连接(outer join)交叉连接(cross join)
多表连接往往要通过表之间的物理外键(或者逻辑外键)进行关联查询。
接下来就分别介绍一下这几个连接的具体用法和区别。
内连接
在数据库多表关联查询中,内连接是最常用的连接类型了,当然也是默认的连接类型。
可以在FROM子句中使用INNER JOIN(INNER关键字可以省略)来实现内连接。
示例: 现在有两张表:订单信息表(orders)和订单详情表(orderdetails),它们的关系如下图所示。
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-1.jpg (41.38 KB, 下载次数: 0)
2020-12-17 15:08 上传
部分数据如下:
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-2.jpg (53.17 KB, 下载次数: 0)
2020-12-17 15:08 上传
订单信息表(orders)
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-3.jpg (43.13 KB, 下载次数: 0)
2020-12-17 15:08 上传
订单详情表(orderdetails)
那么如何根据这两张表关联查询出订单号(orderNumber), 客户编号(customerNumber), 下单日期(orderDate), 订单状态(status), 产品编号(productCode), 下单数量(quantityOrdered), 单价(priceEach)呢?
答案:
SELECT o.orderNumber, o.customerNumber, o.orderDate, o.`status`, od.productCode, od.quantityOrdered, od.priceEachFROM orders o JOIN orderdetails od ON od.orderNumber = o.orderNumber;
查询结果:
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-4.jpg (59.64 KB, 下载次数: 0)
2020-12-17 15:08 上传
外连接
使用外连接时,以主表中每行的数据去匹配从表中的数据行,如果符合连接条件则返回到结果集中;如果没有找到匹配行,则主表的行依旧保留,并且返回到结果集中,相应的从表中的数据行被填上NULL值后也返回到结果集中。
外连接有3种类型,分别是:
左外连接(LEFT OUTER JOIN)右外连接(RIGHT OUTER JOIN)全外连接(FULL OUTER JOIN)
但是,在MySQL中,是不支持全外连接的。这一点要注意。
左外连接
左外连接的结果集中包含左表(JOIN关键字左边的表)中所有的记录,如果右表中没有满足连接条件的记录,则结果集中右表中的相应行数据填充为NULL。
示例:现在有两张表员工信息表(employees)和客户信息表(customers),它们的关系如下图所示:
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-5.jpg (59.91 KB, 下载次数: 0)
2020-12-17 15:08 上传
其中部分数据如下:
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-6.jpg (24.31 KB, 下载次数: 0)
2020-12-17 15:08 上传
员工信息表(customers)
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-7.jpg (29.94 KB, 下载次数: 0)
2020-12-17 15:08 上传
顾客信息表(customers)
那么如何根据这两张表统计出每位员工对应的客户,结果需包含员工号(employeeNumber)、姓名(employeeName)、顾客编号(customerNumber)、顾客姓名(customerName)呢?
答案:
SELECT e.employeeNumber, CONCAT(e.firstName, ' ', e.lastName) AS `employeeName`, c.customerNumber, c.customerNameFROM employees eLEFT JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumber
这里面利用了concat函数来拼接姓名,然后使用分组进行统计。 把employees表作为左表,通过employeeNumber和customers表进行关联。
最终查询结果如下:
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-8.jpg (31.59 KB, 下载次数: 0)
2020-12-17 15:08 上传
在左外连接时,由于部分员工没有顾客,所以顾客的信息会显示为null。
右外连接
右外连接的结果集中,包含满足连接条件的所有数据,此外还包括右表(JOIN关键字右边的表)中不满足条件的数据,这时左表中的相应行数据为NULL。
示例:
同样以左外连接示例中的两表为例,那么如何根据这两张表统计出每位客户对应的销售员工,结果需包含顾客编号(customerNumber)、顾客姓名(customerName)、员工号(employeeNumber)、姓名(employeeName)呢?
很简单,我们只需要将 LEFT JOIN 改为 RIGHT JOIN 即可。
答案:
SELECT e.employeeNumber, CONCAT(e.firstName, ' ', e.lastName) AS `employeeName`, c.customerNumber, c.customerNameFROM employees eRIGHT JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumber
查询出来结果如下:
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-9.jpg (45.1 KB, 下载次数: 0)
2020-12-17 15:08 上传
可以看到,编号是125的顾客是没有对应销售员工的,相应字段数据会显示为null。
那么如何实现全外连接呢?
左表和右表都不做限制,所有的记录都显示,两表不足的地方用null 填充;也就是:
左外连接 = 左表全部记录 + 相关联结果右外连接 = 右表全部记录 + 相关联结果全外连接 = 左表全部记录 + 右表全部记录 + 相关联结果 = 左外连接 + 右外连接 - 相关联结果(即去重复)
那在MySQL中应该怎么做呢?
这就用到了另外一个操作符:UNION,它用于合并两个或多个 SELECT 语句的结果集,一般这样的多个SELECT语句查询出的结果字段都是一样的。默认情况下,UNION操作符是会去除重复数据的。 如果不想去除重复数据,请使用UNION ALL。
同样根据上面的示例,需要查询出所有销售员工和对应的顾客信息,要求顾客有销售员工对应的,要筛选出来,没有对应的销售员工也要把顾客信息查出来,没有顾客的销售员工也查出来。
这时候就需要用到全外连接。
方法是:把左外连接查询和右外连接查询做一个UNION即可。
答案:
SELECT e.employeeNumber, CONCAT(e.firstName, ' ', e.lastName) AS `employeeName`, c.customerNumber, c.customerNameFROM employees eLEFT JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumberUNIONSELECT e.employeeNumber, CONCAT(e.firstName, ' ', e.lastName) AS `employeeName`, c.customerNumber, c.customerNameFROM employees eRIGHT JOIN customers c ON c.salesRepEmployeeNumber = e.employeeNumber
查出来的数据是这样的:
一直疑惑MySQL怎么才能实现全外连接?现在我终于学会了-10.jpg (34.21 KB, 下载次数: 0)
2020-12-17 15:08 上传
可以看出来,是左外连接 + 右外连接 两者的并集去重后的结果。
就这样,MySQL中的全外连接就能实现啦!
如果你觉得本文写的还不错~欢迎点赞、评论、转发、收藏哟!