开发者

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

开发者 https://www.devze.com 2023-03-24 09:10 出处：网络 作者： 自牧君
目录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
相关专题：mysql 内连接mysql外连接sql joins
目录
  • 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

              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 php总共是有107条记录的，还少了 1 个人。原因是在员工表 employees 中，有一个员工的部门编号 department_id 为 (NULL) ，如下图所示：

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

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

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

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

              www.devze.com

              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条，需要使用左外连python接。

              【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`, e开发者_数据库mp.`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条数据；而右表是部门表 departmenjsts ，有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的 UNION 和 UNION 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 OUTE编程R 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的实现

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

              0

              上一篇:

              :下一篇

              更多 数据库 相关资讯：

              精彩评论

              暂无评论...
              登录 注册
              Qedev.com
              验证码 换一张
              取 消

              关注公众号

              热门标签

              图文推荐

              MySQL字符串日期格式转换的几种常用方法例子解析

              MySQL字符串日期格式转换的几种常用方法例子解析
              详解Sqlsugar调用Oracle的存储过程

              详解Sqlsugar调用Oracle的存储过程
              SpringBoot集成Mongodb的操作方法

              SpringBoot集成Mongodb的操作方法
              如何用PyMongo在Python中操作MongoDB的超完整指南

              如何用PyMongo在Python中操作MongoDB的超完整指南
              Vcenter清理/storage/archive空间的处理方式

              Vcenter清理/storage/archive空间的处理方式
              mysql插入带emoji表情的数据报错问题

              mysql插入带emoji表情的数据报错问题
              Mysql允许他人访问本机数据库的实现步骤

              Mysql允许他人访问本机数据库的实现步骤
              Docker安装MongoDB的过程(mongo.latest)

              Docker安装MongoDB的过程(mongo.latest)

              开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

              法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

              Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9