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

1. 内连接

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

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

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

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

查询结果:

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

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

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

2.外连接

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

外连接又分为以下三类:

左外连接:

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

右外连接:

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

满外连接:

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

【例子】根据部门编号 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不支持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 张表共同查询。

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

查询结果:

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

查询结果:

3.2.2 右外连接

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

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

查询结果:

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

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

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

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

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

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

查询结果:

4.2 左外连接

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

查询结果:

4.3 右外连接

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

4.4 第四种JOIN

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;

查询结果:

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

4.5 第五种JOIN

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;

查询结果:

4.6 满外连接

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

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

方法一

方法二

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

查询结果:

4.7 第七种JOIN

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

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内连接和外连接内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql使用left join连接出现重复问题的记录

    目录 mysql使用left join连接出现重复 问题描述 问题示例 问题解决 mysql使用left join时,右表数据有重复数据 总结 mysql使用left join连接出现重复 问题描述 在使用连接查询的时候,例如以A表为主表,左连接B表,我们期望的是A表有多少条记录,查询结果就有多少条记录,但是可能会出现这样的结果,就是查询出来的记录总条数,多余A表的记录总条数,并且是查询的结果显示出来时有部分列是重复的,简单的说,就是产生了笛卡尔积. 问题示例 其中A表是用户表(user),字段

  • MySQL创建数据库和创建数据表

    目录 MySQL 创建数据库和创建数据表 一.数据库操作语言 二.创建数据库 三.创建数据表 四.MySQL 常用字段类型 MySQL 创建数据库和创建数据表 MySQL 是最常用的数据库,在数据库操作中,基本都是增删改查操作,简称CRUD. 在这之前,需要先安装好 MySQL ,然后创建好数据库.数据表.操作用户. 一.数据库操作语言 数据库在操作时,需要使用专门的数据库操作规则和语法,这个语法就是 SQL(Structured Query Language) 结构化查询语言. SQL 的主要

  • IDEA连接MySQL数据库并执行SQL语句使用数据图文详解

    目录 一.IDEA连接MySQL数据库 (一)首先新建普通Java项目 (二)连接数据库 二.使用数据库的数据 (一)新建Java类 Test (二)下载MySQL驱动Jar包 (三)返回IDEA,新建文件夹lib (四)非常重要(添加到库) (五)编写Test类 (六)运行主函数,得到数据 三.总结 一.IDEA连接MySQL数据库 (一)首先新建普通Java项目 (二)连接数据库 1.点击右侧DataBase 2.点击加号,找到MySQL,添加数据库 3.输入用户名和密码,点击Test Co

  • Mysql内连接与外连接的区别详解

    目录 前言 内连接inner join 外连接outer join 左(外)连接 left join 右(外)连接 right join 总结 前言 我在写sql查询的时候,用的最多的就是where条件查询,这种查询也叫内连查询inner join,当然还有外连查询outer join,左外连接,右外连接查询,常用在多对多关系中,那他们区别和联系是什么呢? 内连接inner join 内连接最常用定义: 连接结果仅包含符合连接条件的行组合起来作为结果集,参与连接的两个表都应该符合连接条件使用关键

  • SQL语句的并集UNION 交集JOIN(内连接,外连接)等介绍

    1. a. 并集UNION SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2 b. 交集JOIN SELECT * FROM table1 AS a JOIN table2 b ON a.name=b.name c. 差集NOT IN SELECT * FROM table1 WHERE name NOT IN(SELECT name FROM table2) d. 笛卡尔积 SELECT

  • SQL 四种连接-左外连接、右外连接、内连接、全连接详解

    SQL的四种连接-左外连接.右外连接.内连接.全连接 今天在看一个遗留系统的数据表的时候发现平时查找的视图是FULL OUT JOIN的,导致平时的数据记录要进行一些限制性处理,其实也可以设置视图各表为右外连接并在视图上设置各列的排序和筛选条件就可以达到效果. 联接条件可在FROM或WHERE子句中指定,建议在FROM子句中指定联接条件.WHERE和HAVING子句也可以包含搜索条件,以进一步筛选联接条件所选的行. 联接可分为以下几类: 1.内联接(典型的联接运算,使用像 =  或 <> 之类

  • postgreSQL中的内连接和外连接实现操作

    测试数据: city表: create table city(id int,name text); insert into city values(0,'北京'),(1,'西安'),(2,'天津'),(3,'上海'),(4,'哈尔滨'),(5,'西藏') person表: create table person(id int,lastname char(20)); insert into person values(0,'Tom'),(2,'Lily'),(3,'Mary'),(5,'Coco'

  • MySQL 案例分析讲解外连接语法

    目录 前言 左连接 例 1 右连接 例2 作业记录 前言 外连接可以分为左外连接和右外连接 左外连接: 包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行 右外连接: 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行 左连接 左外连接又称为左连接,使用 LEFT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件. 左连接的语法格式如下: SELECT <字段名> FROM <表1> LEF

  • 关于MySQL与Golan分布式事务经典的七种解决方案

    目录 1.基础理论 1.1 事务 1.2 分布式事务 2.分布式事务的解决方案 2.1 两阶段提交/XA 2.2 SAGA 2.3 TCC 2.4 本地消息表 2.5 事务消息 2.6 最大努力通知 2.7 AT事务模式 3.异常处理 3.1 异常情况 3.2 子事务屏障 3.3 子事务屏障原理 3.4 子事务屏障小结 4.分布式事务实践 4.1 一个SAGA事务 4.2 处理网络异常 4.3 处理回滚 5.总结 前言: 随着业务的快速发展.业务复杂度越来越高,几乎每个公司的系统都会从单体走向分

  • 分享MySQL 主从延迟与读写分离的七种解决方案

    目录 一.强制走主库 二.从库延迟查询 三.判断主从是否延迟?决定选主库还是从库 1.针对这个问题,有什么解决方案? 四.从库节点判断主库位点 五.比较 GTID 六.引入缓存中间件 七.数据分片 1.转换到数据库方面 前言: 我们都知道互联网数据有个特性,大部分场景都是 读多写少,比如:微博.微信.淘宝电商,按照 二八原则,读流量占比甚至能达到 90%. 结合这个特性,我们对底层的数据库架构也会做相应调整.采用 读写分离. 处理过程: 客户端会集成 SDK,每次执行 SQL 时,会判断是 写

  • 详解MySQL数据库--多表查询--内连接,外连接,子查询,相关子查询

    多表查询 使用单个select 语句从多个表格中取出相关的查询结果,多表连接通常是建立在有相互关系的父子表上; 1交叉连接 第一个表格的所有行 乘以 第二个表格中的所有行,也就是笛卡尔积 创建一个消费者与顾客的表格: 代码如下: -- create table customers( -- id int primary key auto_increment, -- name VARCHAR(20)not null, -- address VARCHAR(20)not NULL -- ); -- C

  • 深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接

    1.内联接(典型的联接运算,使用像 =  或 <> 之类的比较运算符).包括相等联接和自然联接.     内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行.例如,检索 students和courses表中学生标识号相同的所有行.       2.外联接.外联接可以是左向外联接.右向外联接或完整外部联接.     在 FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:     1)LEFT  JOIN或LEFT OUTER JOIN     左向外联接的结果集包括  LEF

  • mysql外连接与内连接查询的不同之处

    外连接的语法如下: SELECT 字段名称 FROM 表名1 LEFT|RIGHT|FULL| [OUTER] JOIN 表名2   ON 表名1.字段名1=表面2.字段名2 左外连接 左外连接的结果集包含左表的所有记录和右表中满足连接条件的记录,结果集中那些不符合连接条件的来源于右表的列值为null. LEFT JOIN 右外连接 右外连接是左外连接的反向连接.右外连接的结果集包括右表的所有记录和左表中满足连接条件的记录,结果集中那些不符合连接条件的来源与左表的列值为null. 内连接 内连接

随机推荐