MySQL基础篇补充 | 多表查询中使用SQL99实现7种JOIN操作、SQL99语法新特性

蔚落 2024-03-22 11:48 12阅读 0赞

**目录**

一：多表查询中使用SQL99实现7种JOIN操作

二：SQL99语法新特性

1. 自然连接Natural

2. USING连接

--------------------

## 一：多表查询中使用SQL99实现7种JOIN操作 ##

> 在多表查询中，除了遇到最多的内连接、左外连接和右外连接，还有其它的连接方式；接下来就聊聊其它的连接方式，如下图：

![43db096e32994b7eae3181f2275a838f.png][]

并且在正式讲解之前，需要先了解UNION的使用，因为有些结果集我们可以使用其它结果集合并的方式得到！

**UNION的使用**

> **合并查询结果：**利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

SELECT column,... FROM table1
    UNION [ALL]
    SELECT column,... FROM table2

UNION操作符：返回两个查询的结果集的并集，去除重复记录。

![33e64be043cf45b98dbf360ec13f1444.png][]

UNION ALL操作符：返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

![c49a3dcf2a8645178dcf0f0a26d558ca.png][]

> **注意：**执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

**首先查询三张表：**

员工表emp

![033f0fc92ec24bc1aa0ac64d3c9e8714.png][]

部门表dept

![93b078d1ed6a45ddb7449c6621d2ca9b.png][]

等级表salgrade

![e24b3ac75db741449bb490dd2e4e71d4.png][]

1. 内连接 A∩B---》对应上图3

> 需求：找出每个员工的上级领导？（不包括上级领导为null的King）

SELECT e1.ename '员工',e2.ename '领导'
    FROM emp e1
    INNER JOIN emp e2
    ON e1.mgr = e2.empno;

执行结果：13条数据，只有e1表和e2表ON中等值关系能对上才会显示，相当于A∩B

![6ea8f4536b5a4fbc83f782b73d1350f1.png][]

2. 左外连接---》对应上图1

> 需求：找出每个员工的上级领导？（所有员工必须全部查询出来包括King）

select e1.ename '员工',e2.ename '领导'
    from emp e1
    Left outer join emp e2
    on e1.mgr = e2.empno;

执行结果：14条数据，此时相当于e1表是主表，e2是副表，主表的内容会全部显示；所以KING虽然没有上级领导也会显示

![c4f0140fef7a43be8cf49a31c3bbec13.png][]

3. 右外连接---》对应图2

> 需求：找出每个员工的上级领导？（所有员工必须全部查询出来包括King）

SELECT e1.ename '员工',e2.ename '领导'
    FROM emp e2
    RIGHT OUTER JOIN emp e1
    ON e1.mgr = e2.empno;

执行结果：14条数据，和左外连接差不多，此时还是e1是主表，e2是副表；因为是右外连接，此时把原来两张表的位置调换一下即可

![e3d218effc264a07bc69214e930c29cf.png][]

4. 左中图 A - A∩B ---》对应图4

> 需求：取出上级领导为NULL的员工

SELECT e1.ename '员工',e2.ename '领导'
    FROM emp e1
    LEFT OUTER JOIN emp e2
    ON e1.mgr = e2.empno
    WHERE e2.empno IS NULL;

执行结果：前面我们使用左外连接取出所有的员工对应的领导，所以这里在使用where加一个限制条件就能取出上级领导为NULL的员工；就等同于A - A∩B

![ce98ec4b86174564bb1b8bd945199c66.png][]

5. 右中图 B-A∩B ---》对应图5

> 需求：取出上级领导为NULL的员工

SELECT e1.ename '员工',e2.ename '领导'
    FROM emp e2
    RIGHT OUTER JOIN emp e1
    ON e1.mgr = e2.empno
    WHERE e2.empno IS NULL;

6. 满连接---》对应图6

对于满连接我们可以使用UNION ALL把 “图1和图5” 或者 “图2和图4”拼接在一起就能得到！

SELECT e1.ename '员工',e2.ename '领导'
    FROM emp e1
    LEFT OUTER JOIN emp e2
    ON e1.mgr = e2.empno;
    UNION ALL
    SELECT e1.ename '员工',e2.ename '领导'
    FROM emp e2
    RIGHT OUTER JOIN emp e1
    ON e1.mgr = e2.empno
    WHERE e2.empno IS NULL;

7. 右下图--》对应图7

使用UNION ALL把 “图4和图5” 拼接在一起就能得到！

SELECT e1.ename '员工',e2.ename '领导'
    FROM emp e1
    LEFT OUTER JOIN emp e2
    ON e1.mgr = e2.empno
    WHERE e2.empno IS NULL;
    UNION ALL
    SELECT e1.ename '员工',e2.ename '领导'
    FROM emp e2
    RIGHT OUTER JOIN emp e1
    ON e1.mgr = e2.empno
    WHERE e2.empno IS NULL;

**注：**上面我使用的是表的自连接，例子不太恰当！主要理解原理会掌握使用即可！

## 二：SQL99语法新特性 ##

#### 1. 自然连接Natural ####

> SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ，然后进行等值连接 。

**需求：**显示每个员工信息，并显示所属的部门名称

在SQL92标准中：

SELECT e.ename,d.dname
    FROM emp e,dept d
    WHERE e.deptno = d.deptno;

在 SQL99 中可以写成：

# 使用join...on
    SELECT e.ename,d.dname
    FROM emp e
    JOIN dept d
    ON e.deptno = d.deptno;
    
    # 使用自然连接---就相当于等值连接
    SELECT e.ename,d.dname
    FROM emp e
    NATURAL JOIN dept d;

**优缺点：**虽然简化了连接操作的代码，但是不够灵活，因为它会把两张表或者多张表中所有相同的字段都关联起来；在实际开发中我们可能只需要某一个字段关联，而不是多个字段！

#### 2. USING连接 ####

> ①当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。
> 
> ②实际上USING是自然连接Natural Join的一种优化，但是必须是同名字段才可以这样使用，例如：表的自连接就无法使用，因为不是同一个字段。

# 使用join...on
    SELECT e.ename,d.dname
    FROM emp e
    JOIN dept d
    ON e.deptno = d.deptno;
    
    # 使用USING
    SELECT e.ename,d.dname
    FROM emp e
    JOIN dept d
    USING(deptno);

**总结表连接的约束条件可以有三种方式：WHERE, ON, USING**

> ①WHERE：适用于所有关联查询。
> 
> ②ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
> 
> ③USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等。

[43db096e32994b7eae3181f2275a838f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/985f3e5b01b04374807a16a264e3b497.png
[33e64be043cf45b98dbf360ec13f1444.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/4d4c348cc0c14e69a467044948737a01.png
[c49a3dcf2a8645178dcf0f0a26d558ca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/2b86d6a9397d4fc697d6447d8332bcea.png
[033f0fc92ec24bc1aa0ac64d3c9e8714.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/d5e6ec5d2e90446ca78b07ed9e634bb1.png
[93b078d1ed6a45ddb7449c6621d2ca9b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/13ca6169eb27410ebd3ba87656dbe808.png
[e24b3ac75db741449bb490dd2e4e71d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/61d9721c7f04491bb0596f4aa8eeb3dd.png
[6ea8f4536b5a4fbc83f782b73d1350f1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/865761e6f0e54324b962bb8f0f6807e9.png
[c4f0140fef7a43be8cf49a31c3bbec13.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/a15bfc0093044317986a79970e9cd49f.png
[e3d218effc264a07bc69214e930c29cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/dee9d64fe2ac417fbce4b629eb0f4c55.png
[ce98ec4b86174564bb1b8bd945199c66.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/3b383252aed342b7920d13810ebeaa79.png