0
0

【MySQL】MySQL内连接和外连接详细总结

目录

多表查询的分类3：内连接VS外连接(重难点)

前情回顾：
《多表查询的分类1：等值连接和非等值连接》
《多表查询的分类2：自连接和非自连接》

1. 内连接

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

说人话就是，查询结果只包含它们匹配的行，不匹配的就不要了。

image-20220702101604060

【例子】查询员工编号 

employee_id

和其对应的部门名称 

department_name

。其中部门名称 

department_name

只在部门表 

departments

中，部门表 

departments

如下图所示：

image-20220702095656736

员工表 

employees

和部门表 

departments

通过部门编号 

department_id

匹配连接起来。查询代码如下所示：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp, departments dept
WHERE emp.`department_id`= dept.`department_id`;

查询结果：

image-20220702095037964

这里返回了 106 条记录，但员工表 

employees

总共是有107条记录的，还少了 1 个人。原因是在员工表 

employees

中，有一个员工的部门编号 

department_id

为 (NULL) ，如下图所示：

image-20220628114340768

而部门表 

departments

中却没有值为 (NULL) 的部门编号 

department_id

，因此这一行不匹配的数据就被丢弃不显示了。如下图所示，内连接只包含两个表匹配的行，即下图中两圆相交的部分：

image-20220702101604060
这种连接方式称作内连接。

2.外连接

外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行。

外连接又分为以下三类：

  • 左外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行。如下图中，左外连接就是左边一整个圆。image-20220702103203040
  • 右外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。如下图中，右外连接就是右边一整个圆。image-20220702103412464
  • 满外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表和右表中不满足条件的行。如下图中，满外连接就是两个圆所有部分。image-20220702104033072

【例子】根据部门编号 

department_id

，查询员工表 

employees

中的所有员工编号 

employee_id

和部门表 

departments

中其对应的部门名称 

department_name

。

【分析】凡是题目中出现要求查询 

所有

的字眼时，都要打起十二分精神，这说明需要我们使用外连接查询。实现外连接可使用SQL92和SQL99两种语法，详见[5.9 常用的SQL标准](# 5.9 常用的SQL标准) 。由于左表员工表 

employees

共有 107 条数据，而右表和左表匹配的数据仅有106条，需要使用左外连接。

【SQL92语法实现外连接】使用 

(+)

。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp, departments dept
WHERE emp.`department_id`= dept.`department_id`(+);

查询结果：报错

image-20220702110332445

这是因为MySQL不支持SQL92语法的外连接操作。但是Oracle是支持的。所以没有白学。MySQL只支持SQL99语法来实现多表查询。

3. SQL99语法实现多表查询

SQL99是指SQL在1999年颁布的SQL语法标准规范。尽管在之后发布了一系列新的SQL标准，但在学习MySQL的过程中，主要掌握SQL99和SQL92就已经足够。从这节开始，MySQL的学习就算翻了半篇了，因为这一节之前都是SQL92语法，从这节开始，就专为SQL99语法。

SQL99语法使用 

JOIN...ON

的方式实现多表查询，且可以同时实现内连接和三种外连接。MySQL是支持这种方式的。

3.1 SQL99实现内连接

【例子：三表查询】查询员工的员工编号 

employee_id

、 姓名 

last_name

、部门名称 

department_name

和所在城市 

city

。

【分析】这个需求需要 3 张表共同查询。

image-20220628102343845

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`FROM employees emp JOIN departments dept
ON emp.`department_id`= dept.`department_id`JOIN locations loc
ON dept.`location_id`= loc.`location_id`;

SQL99语法就是加一张表，就 

JOIN

一张表，并在 

ON

后加连接条件。注意，这里的 

JOIN

前面还省略了表示内连接的关键字 

INNER

，在使用内连接时可以忽略。即代码还可以写成完整形式：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`FROM employees emp INNERJOIN departments dept
ON emp.`department_id`= dept.`department_id`JOIN locations loc
ON dept.`location_id`= loc.`location_id`;

查询结果：

image-20220702112216111

3.2 SQL99语法实现外连接

3.2.1 左外连接

【例子】根据部门编号 

department_id

，查询员工表 

employees

中的所有员工编号 

employee_id

和部门表 

departments

中其对应的部门名称 

department_name

。

【分析】由于左表是员工表 

employees

，有107条数据；而右表是部门表 

departments

，有27条数据。题目要求是返回所有员工的107条查询结果，因此这里使用左外连接。SQL99实现左连接接很简单，只需要在 

JOIN

前加上两个关键字 

LEFT OUTER

即可表示左外连接。如下代码所示：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp LEFTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`;

其中，

OUTER

可以省略，即写成：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp LEFTJOIN departments dept
ON emp.`department_id`= dept.`department_id`;

查询结果：

image-20220702113429875

3.2.2 右外连接

举一反三地，右外连接就是在 

OUTER JOIN

前加一个关键字 

RIGHT

。

SELECT emp.`employeed/master/img/d`;

查询结果：

image-20220702114120274

查询结果有122条记录，这怎么解释呢？再回想一下右外连接的定义：

  • 两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。如下图中，右外连接就是右边一整个圆。image-20220702103412464

就不难理解，因为右表部是没有人的。而左、右表匹配的数据有106条 (两圆相交部分) ，因此一共就有 

     106 
    
   
     + 
    
   
     16 
    
   
     = 
    
   
     122 
    
   
  
    106+16=122 
   
  
106+16=122 条记录。如下图所示：

image-20220702115039010

这个例子能更好地帮助我们理解右外连接。

3.2.3 满外连接

举一反三地，满外连接就是在 

OUTER JOIN

前加一个关键字 

FULL

。但很不幸，MySQL不支持SQL99的满外连接语法，Oracle是支持的。

4.总结：七种SQL JOINS的实现

在开始本节之前，需要您了解SQL的 

UNION

和 

UNION ALL

的定义和实现。如果需要了解，可以阅读这篇博文：《MySQL中 UNION 并的使用》。

4.1 内连接

根据部门编号 

department_id

，查询员工表 

employees

中的员工编号 

employee_id

和部门表 

departments

中其对应的部门名称 

department_name

。

image-20220702160726511

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp JOIN departments dept
ON emp.`department_id`= dept.`department_id`;

查询结果：

image-20220702161222953

4.2 左外连接

image-20220702161147277

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp LEFTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`;

查询结果：

image-20220702113429875

4.3 右外连接

image-20220702161536952

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp RIGHTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`;

image-20220702114120274

4.4 第四种JOIN

image-20220702161939516

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp LEFTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`WHERE dept.`department_id`ISNULL;

查询结果：

image-20220702162354954

作用是把员工表 

employees

中，部门编号 

department_id

为 (NULL) 的那一个员工查询出来了，如下图所示：

image-20220628114340768

4.5 第五种JOIN

image-20220702162922037

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, emp.`department_id`FROM employees emp RIGHTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`WHERE emp.`department_id`ISNULL;

查询结果：

image-20220702163303166

4.6 满外连接

由于MySQL不支持SQL99语法的满外连接。因此，我们的实现方式就是求
方法一image-20220702161147277image-20220702162922037方法二image-20220702161536952image-20220702161939516
image-20220702175402115

# 方法一SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp LEFTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`UNIONALLSELECT emp.`employee_id`, emp.`last_name`, dept.`department_id`FROM employees emp RIGHTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`WHERE emp.`department_id`ISNULL;# 方法二SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp RIGHTOUTERJOIN departments dept
ON emp.`employee_id`= dept.`department_id`UNIONALLSELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp LEFTOUTERJOIN departments dept
ON emp.`employee_id`= dept.`department_id`WHERE dept.`department_id`ISNULL;

查询结果：

image-20220702180747613

4.7 第七种JOIN

image-20220702181458219

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp LEFTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`WHERE dept.`department_id`ISNULLUNIONALLSELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`FROM employees emp RIGHTOUTERJOIN departments dept
ON emp.`department_id`= dept.`department_id`WHERE emp.`department_id`ISNULL;

查询结果：

image-20220702182142123

标签： mysql 数据库 java
本文转载自: https://blog.csdn.net/Sihang_Xie/article/details/125571345
版权归原作者 自牧君 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【MySQL】MySQL内连接和外连接详细总结”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航