扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL内连接和外连接及七种SQL JOINS的实现
  • 867

0
分享到

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

mysql内连接mysql外连接sqljoins 2023-03-23 15:03:13 867人浏览 独家记忆
摘要

目录1. 内连接2.外连接左外连接：右外连接：满外连接：3. sql99语法实现多表查询3.1 SQL99实现内连接3.2 SQL99语法实现外连接3.2.1 左外连接3.2.2 右外连接3.2.3 满外连接4.总结：七

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

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-20220702161147277

image-20220702162922037

方法二

image-20220702161536952

image-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

到此这篇关于MySQL内连接和外连接及七种SQL JOINS的实现的文章就介绍到这了,更多相关MySQL内连接和外连接内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

您可能感兴趣的文档:

--结束END--

本文标题: MySQL内连接和外连接及七种SQL JOINS的实现

本文链接: https://lsjlt.com/news/201062.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • MySQL内连接和外连接及七种SQL JOINS的实现
    目录1. 内连接2.外连接左外连接：右外连接：满外连接：3. SQL99语法实现多表查询3.1 SQL99实现内连接3.2 SQL99语法实现外连接3.2.1 左外连接3.2.2 右外连接3.2.3 满外连接4.总结：七...
    99+
    2023-03-23
    mysql 内连接 mysql外连接 sql joins
  • MySQL内连接和外连接及七种SQLJOINS的实现
    目录1. 内连接2.外连接左外连接：右外连接：满外连接：3. SQL99语法实现多表查询3.1 SQL99实现内连接3.2 SQL99语法实现外连接3.2.1 左外连接3.2.2 右...
    99+
    2023-03-23
    mysql 内连接 mysql外连接 sql joins
  • MySQL内连接、外连接及SQL JOINS的实现方法是什么
    这篇文章主要讲解了“MySQL内连接、外连接及SQL JOINS的实现方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL内连接、外连接及SQL JOINS...
    99+
    2023-07-05
  • sql的四种连接——左外连接、右外连接、内连接、全连接
    一、内连接 　　满足条件的记录才会出现在结果集中。 二、 左外连接（left outer join，outer可省略） 左表全部出现在结果集中，若右表无对应记录，则相应字段为NULL 　 举例说明： 客户表： 订单表： 左外连接（...
    99+
    2023-09-10
    sql mysql 数据库
  • 深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接
    内联接（典型的联接运算，使用像 =  或 之类的比较运算符）。包括相等联接和自然联接。      内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。    ...
    99+
    2019-01-06
    深入理解SQL的四种连接-左外连接 右外连接 内连接 全连接
  • SQL内连接、外连接以及(+)号用法
    SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。 A．内连接 内连接，即最常见的等值连接。（两边的表都加限制） B．外连接 外连接分为左外连...
    99+
    2024-04-02
  • SQL中内连接和外连接的区别
    在这篇文章中，我们将了解 SQL 中内连接和外连接的区别。内连接子句使用的是“INNER JOIN”和“JOIN”。它返回两个或多个表的组合元组。当没有共同属性时，结果为空。如果元组数量较多，则“INNER JOIN”比“OUTER”工作得...
    99+
    2023-10-22
  • 详解MySQL的内连接和外连接
    mysql 中的内连接、左外连接和右外连接是用于连接两个或多个表的不同方式，它们之间的区别如下： 内连接（Inner Join）： 内连接也称为等值连接，只返回两个表中键值匹配的行，即只有在两个表中都有匹配的数据时才会返...
    99+
    2023-05-06
    MySQL内连接 MySQL外连接
  • SQL连接的理解和使用（内连接：自然连接&等值连接，外连接：左连接&右连接&全外连接）
    目录 一、连接的介绍连接是什么？连接分几种？条件连接等值连接自然连接 二、连接的使用内连接 INNER JOIN内连接与等值连接区别内连接与自然连接区别 外连接 OUTE...
    99+
    2023-09-17
    sql 数据库 mysql
  • MySQL：七种 SQL JOINS 的实现（图文详解）
    MySQL：7种SQL JOINS的实现 前言一、图示表示二、代码举例1、INNER JOIN（内连接）2、LEFT JOIN（左连接）3、RIGHT JOIN（右连接）4、OUTER JOIN（全连接）5、LEFT EXCLUDI...
    99+
    2023-08-16
    mysql 数据库 sql 大数据
  • 【MySQL】MySQL内连接和外连接详细总结
    目录 多表查询的分类3：内连接VS外连接(重难点)1. 内连接2.外连接3. SQL99语法实现多表查询3.1 SQL99实现内连接3.2 SQL99语法实现外连接3.2.1 左外连...
    99+
    2023-09-04
    mysql 数据库 java sql
  • MySQL中的连接操作：内连接、外连接和交叉连接详解
    MySQL中的连接操作：内连接、外连接和交叉连接详解在MySQL数据库中，连接操作是一种常用的操作技术，用于将两个或多个表中的数据按照一定的条件进行合并。连接操作可以帮助我们处理复杂的数据查询和分析需求。在MySQL中，我们通常使用内连接、...
    99+
    2023-10-22
    连接操作 内连接 外连接
  • 内连接和外连接的区别
    SQL数据库的连接：内连接、和外连接（左外连接、右外连接、和全连接） 本次实验在MySQL数据库中进行，数据库初始表如下 一、内连接：结果仅包含符合连接条件的两表中的行。如下二、外连接：结果包含符合条件的行...
    99+
    2024-04-02
  • 【MySQL系列】表的内连接和外连接学习
    「前言」文章内容大致是对MySQL表的内连接和外连接。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 目录 一、内连接二、外连接2.1 左外连接2.2 右外连接 一、内连接 内连接实际...
    99+
    2023-08-30
    mysql android 数据库
  • MySQL的内连接和外连接有什么区别
    MySQL的内连接和外连接是两种不同的查询方式。 内连接（INNER JOIN）是根据两个表之间的共同字段进行连接，并返回满足条件的...
    99+
    2024-04-09
    MySQL
  • mysql中如何实现外连接转换为内连接
    小编给大家分享一下mysql中如何实现外连接转换为内连接，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ ...
    99+
    2024-04-02
  • mysql内连接和外连接有哪些区别
    这篇文章主要介绍“mysql内连接和外连接有哪些区别”，在日常操作中，相信很多人在mysql内连接和外连接有哪些区别问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql内...
    99+
    2024-04-02
  • 数据库外连接及MySQL实现
    MySQL查询分为内连接查询和外连接查询，他们的区别在于：内连接查询的两个表示对等关系，根据条件进行匹配；外连接是以某一个表为主，两一个表根据条件进行关联。外连接分为左外连接、右外连接和全外连接。本文重点介...
    99+
    2024-04-02
  • oracle中内连接和外连接的区别
    oracle 中的连接类型分为内连接和外连接。内连接仅返回匹配行的结果，而外连接返回匹配行和仅出现在一个表中的行。外连接有三种类型：左外连接（返回左侧表所有行）、右外连接（返回右侧表所有...
    99+
    2024-04-30
    oracle
  • MySQL左连接、右连接、内连接与Hash连接怎么实现
    这篇“MySQL左连接、右连接、内连接与Hash连接怎么实现”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们...
    99+
    2022-11-30
    mysql hash
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作