3.2 基于笛卡尔积理解多表查询——多表查询

  表连接是关系型数据库最突出的特性，通过连接运算符能够实现多表连接查询，查询方式主要包括纵向连接和横向连接。当实际进行MySQL数据库查询时，通常会遇到以下两种情况：① 纵向合并多个表中具有相同字段的数据；② 横向合并多个表中描述同一类事物不同的字段。本章将介绍MySQL多表联合查询方法，其中包括：纵向内连接、纵向全连接、横向内连接、横向左/右连接、横向全连接。

3.2.1 多表纵向连接

  多表纵向连接的作用是将数据表中相同的数据合并到一起，因此纵向连接也可以称为行连接。纵向连接主要有两种连接方式：内连接和全连接，前者将数据合并到一起后剔除整行重复的数据，后者直接将所有数据合并到一起不再去重。本节将使用样例表emp01、emp02进行讲解，数据表创建及插入数据语句如下：

（1）纵向内连接
MySQL中使用union实现纵向内连接，基本语法形式为：

select 字段1,字段2,```,字段n  from table_name1
union
select 字段1,字段2,```,字段n  from table_name2；

【例】查询emp01和emp02中所有不重复的员工信息，输入及输出如下：

小贴士： MySQL进行纵向连接时也能加入其他子句，下面举两个例子：
【例】查询emp01和emp02中所有comm津贴小于8000的员工编号和员工姓名，输入及输出结果如下：

【例】查询emp表中各部门薪资排名前三的员工姓名、员工薪资、部门编号，输入及输出如下：

注意：MySQL进行多表纵向连接时，若只包含where子句可有选择性的添加括号，若包含group by或order by子句则必须添加括号。

（2）纵向全连接 MySQL中使用union all实现纵向全连接，基本语法形式为：

select 字段1,字段2,```,字段n  from table_name1
union all
select 字段1,字段2,```,字段n  from table_name2；

【例】查询emp01和emp02中所有的数据，输入及输出如下：

小贴士：纵向内连接和全连接最大的区别就在于合并后的数据去不去重的问题，句法本身除了union和union all关键字不同，基本可以复用。

3.2.2 多表横向连接

问题A：多表横向连接的作用及常见类型有哪些？
回答A：多表横向连接的作用是将分在不同数据表中描述同一类事物的多个字段合并在一起，因此横向连接也可以称为列连接。横向连接主要有四种连接方式：内连接、左连接、右连接、全连接，连接方式如下图所示：

小贴士：日常工作中有80%的概率会碰到需要横向连接的场景，因此我们日常交流中提及的内连接、全连接默认指的是横向内连接、横向全连接。

问题B：如何编写多表横向连接SQL语句？
回答B：编写多表横向连接SQL语句，将使用样例表manager和employee，数据表结构、创建及插入数据语句如下：

（1）横向内连接
MySQL中一共有3种方法可以实现横向内连接，基本语法形式为：

# 方法1
select 字段1,字段2,```,字段n  from table_name1 join table_name2 on 匹配条件；
# 方法2
select 字段1,字段2,```,字段n  from table_name1 inner join table_name2 on 匹配条件；
# 方法3
select 字段1,字段2,```,字段n  from table_name1,table_name2 where 匹配条件；

注意事项：只有横向内连接-方法3是用where作为匹配条件的语法，其它所有横向连接全以on作为匹配条件的语法。
【例】对表manager和表employee进行横向内连接，输入及输出如下：

小贴士：当遇到较长的表名称时，通常会给数据表起一个临时的别名，MySQL输入及输出如下：

另外两个方法的输入及输出如下：

（2）横向左连接 MySQL中使用left join实现横向左连接，基本语法形式为：

select 字段1,字段2,```,字段n  from table_name1 left join table_name2 on 匹配条件；

【例】对表manager和表employee进行横向左连接，输入及输出如下：

（3）横向右连接 MySQL中使用right join实现横向右连接，基本语法形式为：

select 字段1,字段2,```,字段n  from table_name1 right join table_name2 on 匹配条件；

【例】对表manager和表employee进行横向右连接，输入及输出如下：

小贴士：在日常编写SQL查询语句时，大部分程序员会采用将两张表前后位置颠倒的方法，达到用左连接代替右连接的目标，因此我们很少会看到SQL语句里有右连接的情况。

（4）横向全连接
MySQL中不支持使用full join实现横向全连接，但可以通过将左连接与右连接纵向拼接的方式达到横向全连接的效果。
【例】对表manager和表employee进行横向全连接，输入及输出如下：

问题C：如何理解多表横向连接查询？
回答C：想要理解多表横向连接查询的原理，首先得了解笛卡尔积。
小贴士：笛卡尔积又称为笛卡尔乘积，是两个集合相乘的结果。假设存在集合A{a1,a2,a3}和集合B{b1,b2}，那么集合A与B的笛卡尔积为{(a1,b1),(a1,b2),(a2,b1),(a2,b2),(a3,b1),(a3,b2)}。

  数据库在进行多表横向连接查询时，就是先计算作为匹配条件字段的笛卡尔积，然后筛选出符合规则的元素组合。下面我们举一个横向内连接的例子。
【例】对表manager和表employee进行横向内连接，输入如下：

select * from manager join employee on manager.id=employee.m_id;

基于笛卡尔积的计算方式为：
① 计算作为匹配条件字段id和m_id的笛卡尔积
集合id{1002,2001,3001}
集合m_id{2001,3001}
集合A与B的笛卡尔积为
{(1002,2001),(1002,3001),(2001,2001),(2001,3001),(3001,2001),(3001,3001)}
② 内连接要求筛选出字段id等于m_id的元素组合
{(2001,2001),(3001,3001)}
③ 显示上述结果所查询的字段

再举个横向左连接的实例：
【例】对表manager和表employee进行横向左连接，输入如下：

select * from manager left join employee on manager.id=employee.m_id;

基于笛卡尔积的计算方式为： ① 计算作为匹配条件字段id和m_id的笛卡尔积 集合id{1002,2001,3001}
集合m_id{2001,3001}
集合A与B的笛卡尔积为
{(1002,2001),(1002,3001),(2001,2001),(2001,3001),(3001,2001),(3001,3001)}
② 左连接要求筛选出字段id等于m_id的元素组合，并且保留包含字段id的所有元素
{(2001,2001),(3001,3001)}和{(1002,null)}
③ 显示上述结果所查询的字段

练习

1.查询emp表中各部门薪资小于10000的员工姓名、员工薪资、部门编号
2.查询employee表中所有员工的领导编号和领导姓名
3.基于笛卡尔积描述【例】对表manager和表employee进行横向右连接的数据库计算方式

练习答案

1.第一题的语句如下：

2.第二题的语句如下：
方法1

方法2

方法3

3.第三题的描述如下：
基于笛卡尔积的计算方式为：
（1）计算作为匹配条件字段id和m_id的笛卡尔积
集合id{1002,2001,3001}
集合m_id{2001,3001}
集合A与B的笛卡尔积为
{(1002,2001),(1002,3001),(2001,2001),(2001,3001),(3001,3001),(3001,3001)}
（2）右连接要求筛选出字段id等于m_id的元素组合，并且保留包含字段m_id的所有元素
{(2001,2001),(3001,3001)}
（3）显示上述结果所查询的字段