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

article/2025/9/26 3:44:51

目录

  • 多表查询的分类3：内连接VS外连接(重难点)
  • 1. 内连接
  • 2.外连接
  • 3. SQL99语法实现多表查询
    • 3.1 SQL99实现内连接
    • 3.2 SQL99语法实现外连接
      • 3.2.1 左外连接
      • 3.2.2 右外连接
      • 3.2.3 满外连接
  • 4.总结：七种SQL JOINS的实现
    • 4.1 内连接
    • 4.2 左外连接
    • 4.3 右外连接
    • 4.4 第四种JOIN
    • 4.5 第五种JOIN
    • 4.6 满外连接
    • 4.7 第七种JOIN


多表查询的分类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 INNER JOIN 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 LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

其中，OUTER 可以省略，即写成：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT JOIN 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是支持的。

我们需要使用别的方法实现MySQL中的满外连接，详见4.6 满外连接 。


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 LEFT OUTER JOIN 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 RIGHT OUTER JOIN 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 LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

查询结果：

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 RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

查询结果：

image-20220702163303166


4.6 满外连接

由于MySQL不支持SQL99语法的满外连接。因此，我们的实现方式就是求

4.2 左外连接 和 4.5 第五种JOIN 的并 UNION ALL 即可；或者求4.3 右外连接 和 4.4 第四种JOIN 的并 UNION ALL 也行，都是一样的效果。

方法一image-20220702161147277image-20220702162922037
方法二image-20220702161536952image-20220702161939516

image-20220702175402115

# 方法一
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;# 方法二
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

查询结果：

image-20220702180747613


4.7 第七种JOIN

实现下面这个操作只需要把 4.4 第四种JOIN 和 4.5 第五种JOIN 求 UNION ALL 即可。

image-20220702181458219

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

查询结果：

image-20220702182142123


http://chatgpt.dhexx.cn/article/JCnFg93p.shtml

相关文章

数据库内连接和外连接

数据库内连接和外连接

内连接 内连接使用关键字INNER JOIN 也可以简写成JOIN；内连接只返回两个表中匹配的数据。 SELECT a.id, a.name,b.price FROM table1 AS a INNER JOIN table2 AS b ON a.id b.id; 外连接 左外连接 用关键字LEFT OUTER JOIN 表示 也可以简写为LFFT JOIN。 以左边的…
阅读更多...
oracle内连接和外连接

oracle内连接和外连接

一、表的连接 1.内连接 （inner join） 做内连接得到的是a和b的关联关系的列同时存在的进行连接，内连后a和b关联列相同的a中数据和b中数据合在一起形成新的表数据。 内连接只有满足条件的数据才会显示 -- a 表和 b 表做内连接 -- a 表中的…
阅读更多...
mysql内外三种连接,Mysql的内外连接方式

mysql内外三种连接,Mysql的内外连接方式

Mysql有很多种连接方式，其中有两大主要连接方式：内连接和外连接，其中外连接又可以分为左外连接和右外连接； 对于这三种连接方式，如何判断何时使用一直困扰着我，这里就做一下自己的总结： 1、内连…
阅读更多...
SQL内连接

SQL内连接

开发工具与关键技术： sql 撰写时间：2022/3/27 内连接（可多个表连接）：两表合并为1表 内连接格式1： select 字段 from 表1，表2 where 连接条件 或者 ： select 字段 from 表1…
阅读更多...
MySql 内连接

MySql 内连接

三.内连接 内链接:两张或者多张表中按给定的连接条件查询满足条件的结果 -- 1.语法: select 查询内容 -- from table1,table2 where 关联条件 列如: 查询雇员信息级雇员所在部门信息 select * from emp,dept where emp.deptnodept.deptno 使用别名: 列如…
阅读更多...
mysql内连接

mysql内连接

文章目录 多表连接一、内连接初识（内连接两张表）二、内连接多张表三、为表起别名 题型综合 多表连接 多表连接的基础，要连接的表必须具有相同意义的字段（有关系的表）； 多表连接什么时候用：顾名…
阅读更多...
ClickHouse内连接

ClickHouse内连接

A内连接B>S2区域 SELECT date, ts, country, province, city,ye_no FROM (SELECT date, ts, country, province, city,0 AS yes_no FROM DBa.tba WHERE (date today()-1) ) ALL INNER JOIN (SELECT date, ts, country, province, city FROM DBb.tbb WHERE (date tod…
阅读更多...
内连接，左连接，右连接作用及区别（数据库相关九）

内连接，左连接，右连接作用及区别（数据库相关九）

我们来看个例子： 内连接就是普通的查询，例如 1.加inner join的： SELECT S.SName,C.CourseID,C.Grade From Sc AS C INNER JOIN Students AS S ON C.StudentID S.SNo 2.不加inner join的 SELECT Students.SName,…
阅读更多...
知识点 左连接、右连接、内连接

知识点 左连接、右连接、内连接

为什么使用 这些X连接？ 答：因为 我们想看到更多维度的数据，原本这些数据其实应该在一个表的，但为了避免数据冗余、面向对象（一张表一般对应一个实体类）等因素，我们把这些数据根据某个规则进行拆…
阅读更多...
inner join 和outer join

inner join 和outer join

内连接（INNER JOIN） 内连接也称为等同连接，返回的结果集是两个表中所有相匹配的数据，而舍弃不匹配的数据。也就是说，在这种查询中，DBMS只返回来自源表中的相关的行，即查询的结果表包含的两源表…
阅读更多...
SQL连接表(内连接、左连接、右连接、交叉连接、全外连接)

SQL连接表(内连接、左连接、右连接、交叉连接、全外连接)

分享一些自己的连表总结 什么是连接表？ 多表查询原理：将多个表通过笛卡尔积形成一个虚表，再根据查询条件筛选符合条件的数据。 在关系数据库中，数据分布在多个逻辑表中。 要获得完整有意义的数据集，需要使用连接来查询…
阅读更多...
内连接与外连接（全网最详细）

内连接与外连接（全网最详细）

目录 前言 一、内连接 二、外连接 1.左外连接 2.右外连接 3.满外连接 三、UNION的使用 四、7种SQL JOINS的实现 前言 在学习内连接与外连接之前，你不妨思考为什么要引入这两种连接方式，带着问题去学习，更有助于我们对知识的学习。 其…
阅读更多...
LDF explorer使用问题记录

LDF explorer使用问题记录

本文章并不是使用教程！ 问题1：信号必须要设置“Unit”值 当信号时枚举值或其他非物理信号，我们不为其设置Unit属性时，通过NCF工具生成源文件时，会报错，如下图所示： 通过文本方式打开ldf文件…
阅读更多...
SqlServer如何导入mdf、ldf文件

SqlServer如何导入mdf、ldf文件

1、sqlserver数据库中可以直接拷贝mdf和ldf文件对sqlserver数据库中的数据进行备份，然后通过数据库管理软件加载mdf和ldf文件加载数据库数据。 2、如果拷贝mdf和ldf文件失败时可以先在服务中停止SQL Server(MSSQLSERVER)服务 3、如下两种方法亲证都可行 第一种方法…
阅读更多...
SqlServer2008Ldf文件过大处理

SqlServer2008Ldf文件过大处理

ldf文件是数据库的操作日志文件，用于数据库的数据恢复； 缩小体积的方法： 第一步：设置数据库成简单模式，在master下执行 ALTER DATABASE databasename SET RECOVERY SIMPLE; 第二步：缩小数据库体积到1024MB …
阅读更多...
SqlServer怎么导入mdf、ldf文件

SqlServer怎么导入mdf、ldf文件

第一种方法: 1.打开SQL Server，写好登录名和密码点击连接. 2.打开数据库，右键某一个数据库，选择“新建查询（Q）”。 3.再代码界面输入如下代码，点击F5键或者点击运行按钮即可。 代码如下： EXEC…
阅读更多...
sql server处理/压缩LDF文件

sql server处理/压缩LDF文件

LDF/MDF文件太大了，要压缩处理 SELECT NAME, recovery_model_desc FROM sys.databases 查看是什么模式这里的DNName_Log 如果不知道在sys.database_files里是什么名字的话，可以用以下注释的语句进行查询 USE DNName GO SELECT file_id, nameFROM s…
阅读更多...
服务器mdf ldf文件,sqlserver2008如何导入mdf，ldf文件

服务器mdf ldf文件,sqlserver2008如何导入mdf，ldf文件

网上找了很多解决sql server导入其他电脑拷过来的mdf文件，多数是不全，遇到的解决方法不一样等问题，下边是找到的解决问题的最全面方法！ 将mdf， ldf文件导入到sql server 2008数据库当中，下面提供两种办法: …
阅读更多...
ldf文件怎么储存到mysql中_sqlserver数据库的ldf文件太大，我们如何压缩ldf日志文件...

ldf文件怎么储存到mysql中_sqlserver数据库的ldf文件太大，我们如何压缩ldf日志文件...

使用sqlserver数据库的数据有两个文件，分别是mdf和ldf。 mdf是数据文件，另外一个ldf为日志文件。随着网站的运行，你会发现ldf这个文件会越来越大，我们如何压缩ldf日志文件。 网上有很多收缩ldf文件的方法，但比较杂乱&a…
阅读更多...
SQLServer数据库丢失ldf文件的恢复

SQLServer数据库丢失ldf文件的恢复

某个数据库例如CXPT2，只把CXPT2.mdf文件留下来了，ldf文件没有留下来， 启动的时候显示显示 CXPT2[可疑]，不知道怎么解决，这时候CXPT2.mdf目录下有一个旧的ldf文件， 后来看了网上的帖子，就把ldf…
阅读更多...
推荐文章

Copyright @ 2022~2023