MySQL内连接和外连接及七种SQL JOINS的实现

作者:自牧君 时间:2024-01-21 09:23:16 

MySQL内连接和外连接及七种SQL JOINS的实现

1. 内连接

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

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

MySQL内连接和外连接及七种SQL JOINS的实现

【例子】查询员工编号 employee_id 和其对应的部门名称 department_name 。其中部门名称 department_name 只在部门表 departments 中,部门表 departments 如下图所示:

MySQL内连接和外连接及七种SQL JOINS的实现

员工表 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`;

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

这里返回了 106 条记录,但员工表 employees 总共是有107条记录的,还少了 1 个人。原因是在员工表 employees 中,有一个员工的部门编号 department_id 为 (NULL) ,如下图所示:

MySQL内连接和外连接及七种SQL JOINS的实现

而部门表 departments 中却没有值为 (NULL) 的部门编号 department_id ,因此这一行不匹配的数据就被丢弃不显示了。如下图所示,内连接只包含两个表匹配的行,即下图中两圆相交的部分:

MySQL内连接和外连接及七种SQL JOINS的实现


这种连接方式称作内连接。

2.外连接

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

外连接又分为以下三类:

左外连接:

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

MySQL内连接和外连接及七种SQL JOINS的实现

右外连接:

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

MySQL内连接和外连接及七种SQL JOINS的实现

满外连接:

两个表在连接过程中除了返回满足连接条件的行以外,还返回左表和右表中不满足条件的行。如下图中,满外连接就是两个圆所有部分。

MySQL内连接和外连接及七种SQL JOINS的实现

【例子】根据部门编号 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`(+);

查询结果:报错

MySQL内连接和外连接及七种SQL JOINS的实现

这是因为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 张表共同查询。

MySQL内连接和外连接及七种SQL JOINS的实现

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`;

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

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`;

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

3.2.2 右外连接

举一反三地,右外连接就是在 OUTER JOIN 前加一个关键字 RIGHT

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

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

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

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

MySQL内连接和外连接及七种SQL JOINS的实现

就不难理解,因为右表部是没有人的。而左、右表匹配的数据有106条 (两圆相交部分) ,因此一共就有 106 + 16 = 122 106+16=122 106+16=122 条记录。如下图所示:

MySQL内连接和外连接及七种SQL JOINS的实现

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

3.2.3 满外连接

举一反三地,满外连接就是在 OUTER JOIN 前加一个关键字 FULL 。但很不幸,MySQL不支持SQL99的满外连接语法,Oracle是支持的。

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

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

在开始本节之前,需要您了解SQL的 UNIONUNION ALL 的定义和实现。如果需要了解,可以阅读这篇博文:《MySQL中 UNION 并的使用》。

4.1 内连接

根据部门编号 department_id ,查询员工表 employees 中的员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name

MySQL内连接和外连接及七种SQL JOINS的实现

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

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

4.2 左外连接

MySQL内连接和外连接及七种SQL JOINS的实现

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`;

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

4.3 右外连接

MySQL内连接和外连接及七种SQL JOINS的实现

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`;

MySQL内连接和外连接及七种SQL JOINS的实现

4.4 第四种JOIN

MySQL内连接和外连接及七种SQL JOINS的实现

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;

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

作用是把员工表 employees 中,部门编号 department_id 为 (NULL) 的那一个员工查询出来了,如下图所示:

MySQL内连接和外连接及七种SQL JOINS的实现

4.5 第五种JOIN

MySQL内连接和外连接及七种SQL JOINS的实现

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;

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

4.6 满外连接

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

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

方法一

MySQL内连接和外连接及七种SQL JOINS的实现

MySQL内连接和外连接及七种SQL JOINS的实现

方法二

MySQL内连接和外连接及七种SQL JOINS的实现

MySQL内连接和外连接及七种SQL JOINS的实现

MySQL内连接和外连接及七种SQL JOINS的实现

# 方法一
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;

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

4.7 第七种JOIN

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

MySQL内连接和外连接及七种SQL JOINS的实现

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;

查询结果:

MySQL内连接和外连接及七种SQL JOINS的实现

来源:https://blog.csdn.net/Sihang_Xie/article/details/125571345

标签:mysql,内连接,外连接,sql,joins
0
投稿

猜你喜欢

  • pyecharts实现数据可视化

    2023-05-24 06:18:48
  • 如何做一个可以让人家申请使用的计数器?

    2010-07-11 21:17:00
  • python 识别登录验证码图片功能的实现代码(完整代码)

    2021-03-14 23:03:40
  • Go打包二进制文件的实现

    2024-05-08 10:15:19
  • 详解.NET数据库连接池

    2024-01-20 16:05:59
  • 新手入门学习python Numpy基础操作

    2023-10-08 07:46:27
  • 纯JS实现五子棋游戏兼容各浏览器(附源码)

    2024-04-25 13:14:53
  • python数据类型相关知识扩展

    2021-12-21 10:20:07
  • 对Python w和w+权限的区别详解

    2022-11-30 09:40:15
  • Python 根据相邻关系还原数组的两种方式(单向构造和双向构造)

    2023-09-24 17:08:06
  • Python OpenCV 图像区域轮廓标记(框选各种小纸条)

    2023-12-18 05:42:08
  • Django跨域请求CSRF的方法示例

    2021-07-13 21:09:34
  • Python设计模式之工厂模式简单示例

    2022-10-06 13:22:38
  • adox 的vbs类,提取表名,列名等

    2008-07-02 12:37:00
  • 优化你的ASP程序及优化网页

    2007-10-06 23:02:00
  • PHP实现的简单排列组合算法应用示例

    2023-11-18 16:28:40
  • 通过源码分析Python中的切片赋值

    2021-06-26 16:31:04
  • Python3.7基于hashlib和Crypto实现加签验签功能(实例代码)

    2023-03-25 16:23:00
  • oracle日志操作模式(归档模式和非归档模式的利与弊)

    2024-01-24 18:01:36
  • MySQL系列之九 mysql查询缓存及索引

    2024-01-22 12:46:10
  • asp之家 网络编程 m.aspxhome.com