【MySQL】多表查询（笛卡尔积现象，联合查询、内连接、左外连接、右外连接、子查询）-通过练习快速掌握法

在DQL的基础查询中，我们已经学过了多表查询的一种：联合查询（union）。本文我们将系统的讲解多表查询。

笛卡尔积现象

首先，我们想要查询emp表和stu表两个表，按照我们之前的知识栈，我们直接使用：

sql">select * from emp,stu;

当查询emp时：15条记录被查询

当查询stu时：5条记录被查询

但是让我们来观察结果：

哇，查询到了70条记录。而且根据结果我们可以看出：左表emp的每条记录都会与右表stu的每条记录组成一条新的记录，也就是14*5=70条记录。这种现象非常符合离散数学中学到的笛卡尔积的结果，所以我们将这种现象称为笛卡尔积现象。

笛卡尔积（Cartesian Product）：表示两个集合之间的所有可能的有序对的集合

笛卡尔积的性质包括：

笛卡尔积的结果是一个新集合。
如果 AA 和 BB 其中一个为空集，则结果也为空集。
笛卡尔积的顺序是重要的，即 A×B≠B×A。

我们如何实现15+4的结果呢？直接使用上面的select肯定是不行了。

那么，此时有一个叫联合查询的方式出现在脑海里：

联合查询

关键字：【union all】

sql">select empno,ename,job from emp
union all
select id,nick,pwd from stu;

观察结果：19=15+4条记录（使用union代替union all可以实现去重的功能）

但是为了将记录查询出来，我们必须合适选择每个表的字段，将两个表查询的字段的数据类型一一对应。empno int = id int，ename varchar = nick varchar ......

如果数据类型对应不上，那么将无法查询，结果是：

直接查询的条件限制法

那么联合查询也不能符合我们对查询结果的预期，这时候需要我们转换思路。从笛卡尔积现象开始：【需求：查询员工表以及每个员工对应的部门信息】

首先直接查询：

对于查询的结果，虽然有重复，但至少有我们需要的结果，那么只需要将这个表中的有效记录提取出来，就可以了。也就是使用where条件进行限定：

此时我们查询的结果就符合我们的预期了。但注意，这时候我们操作时必须给每个字段指定上是哪个表的字段，不然的话，该字段属于二义性字段，无法通过语法分析，也就不能执行了。

内连接

sql">select field from tb1 
[inner] join tb2 on condition;

等值连接

eg.根据一个编号查另一个表中改编号对应的内容。常见于：根据子表外键连接父表主键

【练习：查询员工表以及每个员工对应的部门信息】

sql">select * from emp [inner]join dept on emp.DEPTNO = dept.DEPTNO;

非等值连接

eg.根据一个表的某个字段，查另一个表中该字段属于哪段区间的信息。实际用途：等级划分

【练习：根据员工的薪水查出薪水的等级】

自连接

eg.自连接是某个表的某个字段信息存储的数据是本表的另一条记录的信息。常用于：事物关联

【练习：根据员工表的领导编号查询领导的名字】

自连接的流程：为显示的字段起别名(避免两个结果字段名冲突，非必须)=》from选择查询表=》join 连接表（本表），并起别名（避免二义性，必须）=》连接条件。[过程中的每个字段都需要明确指出是哪个表]

外连接

由于内连接会将连接条件的字段中空值的记录给过滤掉，所以为了显示较为全面的记录，我们采用外连接的方式进行多表查询。

左外连接

左外连接就是(left [outer] join ... on...)。显示主表的所有字段，并将被连接的从表符合连接条件的记录连接到主表，如果没有，主表显示原本记录，从表的字段中为空。

【练习：查询员工表以及每个员工对应的部门信息---显示所有员工】

右外连接

右外连接就是(right [outer] join ... on...)。与左外连接类似。

【练习：查询员工表以及每个员工对应的部门信息---显示所有部门】

我们对比发现，右外连接显示的记录比左外连接的记录多一条，多出的一条是部门表中的数据，但该部门在员工表中没有员工，所以全部显示为空。

注：外连接查询的结果记录数 >= 内连接查询到的结果记录数

左外连接【左图】、右外连接【右图】

子查询

子查询：嵌套在其它SQL语句内的查询语句，且必须出现在圆括号内（查询一般是指select语句）：子查询的结果可以作为外层查询的过滤条件或计算字段。

标量子查询

子查询返回结果是单个值，如数字、字符串、日期等最简单的形式。这种子查询称为标量子查询。【常用的操作符：| = | <> | > | >= | < | <= |】

【练习：查询销售部的部门员工信息】

第一步：查询销售部的部门编号

sql">select deptno from dept where dname="SALES";

第二步：查询部门编号为上述结果的员工

sql">select * from emp where deptno = 上条语句的结果;

第三步：合并一条语句：

sql">select * from emp where deptno = (select deptno from dept where dname="SALES");

标量子查询可以在子句中使用聚合函数、而且子句的位置还可以出现在select后作为字段出现：

【练习：查询部门名，以及每个部门的人数】

sql">select dname, (select count(*) from emp where dept.deptno=emp.deptno) emps 
from dept;

列子查询

子查询的结果是一列(或者多列)，这种子查询称为列子查询。

【常用操作符：in、not in、any、some、all】

IN：在指定的集合范围之内，多选一

NOT IN：不再指定的集合范围之内

ANY：子查询返回列表中，有任意一个满足即可【相当于集合所有元素作 or 运算】

SOME：与ANY相同，SOME与ANY等价

ALL：子查询返回列表的所有值都要满足【相当于集合所有元素之间作 and 运算】

【练习：查询销售部（SALES）和调研部（RESEARCH）所有员工信息】

sql">select * 
from emp 
where deptno in (select deptno from dept where dname in ("SALES","RESEARCH"));-- or:
select * 
from emp 
where deptno in (select deptno from dept where dname="SALES" or dname="RESEARCH");

【练习：查询比销售部的所有人的工资都高的员工信息】

比所有人都高，也就是sal > all( {...} )

通过这个练习，我们不仅练习了all运算，我们还知道了，子句可以嵌套子句。

行子查询

子查询的返回结果是一行（可以是多行），这种子查询称为行子查询。

【常用操作符：| = | <> | in | not in】

【练习：查询与“SMITH”的薪资以及直属领导都相同的员工信息】

sql">-- (单行结果)
select * from emp where (sal,mgr) = (select sal,mgr from emp where ename = "SMITH");-- (多行结果)
select * from emp where (sal,mgr) in (select sal,mgr from emp where ename = "SMITH");

通过该练习，我们掌握了新的知识：

(field1,field2,...,fieldn) 可以通过加圆括号的方式直接与行结果进行运算【= | <> | in | not in】

表子查询

子查询的结果可以是多行多列，产生这种结果的子查询称为表子查询。【常用操作符：IN】

这种就是行子查询的 in 操作。

sql">-- (多行结果)
select * from emp where (sal,mgr) in (select sal,mgr from emp where ename = "SMITH");

感谢大家！欢迎指导、询问、探讨知识！