RDBMS多表查询技术

第6章​ 多表查询

本章学习目标

​ 理解表与表之间的关系

​ 熟练掌握合并结果集

​ 熟练掌握连接查询

​ 熟练掌握子查询

前面章节学习了单表查询，但是业务复杂时，会涉及到多表查询，本章将详细讲解多表查询的相关内容。

6.1​ 表与表之间的关系

在讲解多表查询前，首先要了解表与表之间的关系，以及在哪种情况下应该将表设计成什么关系，这对以后的多表操作学习有很大帮助，表与表之间的关系主要包括一对一、一对多（多对一）和多对多，其中一对多和多对一实际上是一样的，只不过从不同的角度来看，接下来详细讲解表与表之间的关系。

6.1.1​ 一对一

在一对一关系中，关系表的每一边都只能存在一条记录，每个数据表中的关键字在对应的关系表中只能存在一条记录或者没有对应的记录，这种关系与现实生活中配偶的关系很像，如果已经结婚，那么只有一个配偶，如果没有结婚，那么没有配偶，为了加深理解，接下来通过具体案例演示一对一的关系。

首先需要创建用户表user，表结构如表6.1所示。

表6.1​ user表

字段	字段类型	约束类型	说明
uid	INT	PRIMARY KEY	用户编号
uname	VARCHAR(20)		用户名称
usex	VARCHAR(20)		用户性别
uaddress	VARCHAR(50)		用户地址

表6.1中列出了user表的字段、字段类型、约束类型和说明，接着创建user表，SQL语句如下所示。

mysql> CREATE TABLE user(

-> uid INT PRIMARY KEY,

-> uname VARCHAR(20),

-> usex VARCHAR(20),

-> uaddress VARCHAR(50)

-> );

Query OK, 0 rows affected (0.14 sec)

以上执行结果证明表user创建完成，接着创建user_text表，表结构如表6.2所示。

表6.2​ user_text表

字段	字段类型	约束类型	说明
uid	INT	PRIMARY KEY FOREIGN KEY	用户编号
utext	VARCHAR(100)		用户备注

表6.2中列出了user_text表的字段、字段类型、约束类型和说明，接着创建user_text表，SQL语句如下所示。

mysql> CREATE TABLE user_text(

-> uid INT PRIMARY KEY,

-> utext VARCHAR(100),

-> FOREIGN KEY(uid)

-> REFERENCES user(uid)

-> );

Query OK, 0 rows affected (0.08 sec)

以上执行结果证明表user_text创建完成，接下来通过图示来直观地理解两张表的关系，如图6.1所示。

图6.1​ 一对一关系

从图6.1中可以看到，user表与user_text表是一对一的关系，这里用一对一的意义实际上是数据库优化，用户备注的字段utext一般是比较多的文字，属于大文本字段，但这个字段又不是每次都要用到，如果存放到user表中，在查询用户数据的时候，会影响user表的查询效率，所以将utext字段单独拆分出来，放到从表当中，需要utext字段时，进行两张表的关联查询即可。

6.1.2​ 一对多（多对一）

在一对多关系中，主键数据表中只能含有一个记录，而在其关系表中这条记录可以与一个或者多个记录相关，也可以没有记录与之相关，这种关系与现实生活中父子的关系很像，每个孩子都有一个父亲，但一个父亲可能有多个孩子，也可能没有孩子，多对一是从不同角度来看问题，例如从孩子的角度来看，一个孩子只能有一个父亲，多个孩子也可能是同一个父亲，这里针对一对多讲解，为了加深理解，接下来通过具体案例演示一对多的关系。

首先需要创建学生表student，表结构如表6.3所示。

表6.3​ student表

字段	字段类型	约束类型	说明
stu_id	INT	PRIMARY KEY	学生编号
stu_name	VARCHAR(20)		学生姓名

表6.3中列出了student表的字段、字段类型、约束类型和说明，接着创建student表，SQL语句如下所示。

mysql> CREATE TABLE student(

-> stu_id INT PRIMARY KEY,

-> stu_name VARCHAR(20)

-> );

Query OK, 0 rows affected (0.08 sec)

以上执行结果证明表student创建完成，接着创建score表，表结构如表6.4所示。

表6.4​ score表

字段	字段类型	约束类型	说明
sco_id	INT	PRIMARY KEY	分数编号
score	INT		学生分数
stu_id	INT	FOREIGN KEY	学生编号

表6.4中列出了score表的字段、字段类型、约束类型和说明，接着创建score表，SQL语句如下所示。

mysql> CREATE TABLE score(

-> sco_id INT PRIMARY KEY,

-> score INT,

-> stu_id INT,

-> FOREIGN KEY(stu_id)

-> REFERENCES student(stu_id)

-> );

Query OK, 0 rows affected (0.08 sec)

以上执行结果证明表score创建完成，接下来通过图示来直观地理解两张表的关系，如图6.2所示。

图6.2​ 一对多关系

从图6.2中可以看到，student表与score表是一对多的关系，每一个学生可能有多个成绩，但一个成绩只能属于一个学生，这就是一对多的关系，同样的，如果从score表来看问题，多个成绩可以属于一个学生，但一个成绩不能属于多个学生，这就是多对一的关系。

6.1.3​ 多对多

在多对多关系中，两个数据表里的每条记录都可以和另一个数据表里任意数量的记录相关，这种关系与现实生活中学生与选修课的关系很像，一个学生可以有多门选修课，一门选修课也可以有多个学生，为了加深理解，接下来通过具体案例演示多对多的关系。

首先需要创建教师表teacher，表结构如表6.5所示。

表6.5​ teacher表

字段	字段类型	约束类型	说明
tea_id	INT	PRIMARY KEY	教师编号
tea_name	VARCHAR(20)		教师姓名

表6.5中列出了teacher表的字段、字段类型、约束类型和说明，接着创建teacher表，SQL语句如下所示。

mysql> CREATE TABLE teacher(

-> tea_id INT PRIMARY KEY,

-> tea_name VARCHAR(20)

-> );

Query OK, 0 rows affected (0.08 sec)

以上执行结果证明表teacher创建完成，接着创建stu表，表结构如表6.6所示。

表6.6​ stu表

字段	字段类型	约束类型	说明
stu_id	INT	PRIMARY KEY	学生编号
stu_name	VARCHAR(20)		学生姓名

表6.6中列出了stu表的字段、字段类型、约束类型和说明，接着创建stu表，SQL语句如下所示。

mysql> CREATE TABLE stu(

-> stu_id INT PRIMARY KEY,

-> stu_name VARCHAR(20)

-> );

Query OK, 0 rows affected (0.09 sec)

以上执行结果证明表stu创建完成，最后还需要创建一张关系表tea_stu，用于映射多对多的关系，表结构如表6.7所示。

表6.7​ tea_stu表

字段	字段类型	约束类型	说明
tea_id	INT	FOREIGN KEY	教师编号
stu_id	INT	FOREIGN KEY	学生编号

表6.7中列出了tea_stu表的字段、字段类型、约束类型和说明，接着创建tea_stu表，SQL语句如下所示。

mysql> CREATE TABLE tea_stu(

-> tea_id INT,

-> stu_id INT,

-> FOREIGN KEY(tea_id)

-> REFERENCES teacher(tea_id),

-> FOREIGN KEY(stu_id)

-> REFERENCES stu(stu_id)

-> );

Query OK, 0 rows affected (0.09 sec)

以上执行结果证明表tea_stu创建完成，接下来通过图示来直观地理解三张表的关系，如图6.3所示。

图6.3​ 多对多关系

从图6.3中可以看到，teacher表与stu都与中间表tea_stu关联，且都是一对多的关系，所以teacher与stu表是多对多的关系，即一个老师可以有多个学生，一个学生也可以有多个老师，这就是多对多的关系的应用场景。

6.2​ 合并结果集

在多表查询中，合并结果集也是常用的查询方法，合并结果集又分为UNION和UNION ALL两种，接下来针对这两种查询方式进行讲解。

6.3.1​ UNION

在多表查询中，有时可能需要将两条查询语句的结果合并到一起，MySQL提供了UNION关键字用于合并结果集，接下来通过一个案例演示UNION关键字的用法。

例6-1​ 创建测试表test1，表结构如表6.8所示。

表6.8​ test1表

字段	字段类型	约束类型	说明
id	INT	PRIMARY KEY	编号
name	VARCHAR(20)		姓名

创建表并添加约束，SQL语句如下所示。

mysql> CREATE TABLE test1(

-> id INT PRIMARY KEY,

-> name VARCHAR(20)

-> );

Query OK, 0 rows affected (0.09 sec)

以上执行结果证明表test1创建完成，接着为表添加数据，SQL语句如下所示。

mysql> INSERT INTO test1(

-> id,

-> name) VALUES(

-> 1,

-> 'zs'

-> );

Query OK, 1 row affected (0.07 sec)

以上执行结果证明表test1数据插入成功。

例6-2​ 创建测试表test2，表结构如表6.9所示。

表6.9​ test2表

字段	字段类型	约束类型	说明
id	INT	PRIMARY KEY	编号
name	VARCHAR(20)		姓名

创建表并添加约束，SQL语句如下所示。

mysql> CREATE TABLE test2(

-> id INT PRIMARY KEY,

-> name VARCHAR(20)

-> );

Query OK, 0 rows affected (0.08 sec)

以上执行结果证明表test1创建完成，接着为表添加数据，SQL语句如下所示。

mysql> INSERT INTO test2(

-> id,

-> name) VALUES(

-> 1,

-> 'ls'

-> );

Query OK, 1 row affected (0.04 sec)

以上执行结果证明表test2数据插入成功。

例6-3​ 查询表test1和表test2的数据，并将查询出的结果集合并，SQL语句如下所示。

mysql> SELECT * FROM test1

-> UNION

-> SELECT * FROM test2;

+----+------+

| id | name |

+----+------+

| 1 | zs |

| 1 | ls |

+----+------+

2 rows in set (0.03 sec)

从以上执行结果可看出，查询出的表test1和表test2的数据合并了结果集，如果两张表有相同的数据，UNION关键字会去除重复的数据，接下来演示这种情况，首先分别向test1表和test2表中添加一条相同的数据，SQL语句如下所示。

mysql> INSERT INTO test1(

-> id,

-> name) VALUES(

-> 2,

-> 'abc'

-> );

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO test2(

-> id,

-> name) VALUES(

-> 2,

-> 'abc'

-> );

Query OK, 1 row affected (0.04 sec)

以上执行结果证明添加数据成功，接着再次使用UNION查询查询两张表的数据，SQL语句如下所示。

mysql> SELECT * FROM test1

-> UNION

-> SELECT * FROM test2;

+----+------+

| id | name |

+----+------+

| 1 | zs |

| 2 | abc |

| 1 | ls |

+----+------+

3 rows in set (0.00 sec)

从以上执行结果可看出，两张表的所有数据查出且合并了结果集，但两张表中的重复数据被过滤掉。

6.3.2​ UNION ALL

前面学习了UNION关键字的用法，UNION ALL关键字与之类似，区别就是UNION ALL关键字查出两张表的数据合并结果集后，不会过滤掉重复的数据，这里直接使用前面创建的表test1和表test2进行演示。

例6-4​ 查询表test1和表test2的数据，并将查询出的结果集合并，不过滤重复数据，SQL语句如下所示。

mysql> SELECT * FROM test1

-> UNION ALL

-> SELECT * FROM test2;

+----+------+

| id | name |

+----+------+

| 1 | zs |

| 2 | abc |

| 1 | ls |

| 2 | abc |

+----+------+

4 rows in set (0.00 sec)

从以上执行结果可看出，两张表的所有数据查出且合并了结果集，但两张表中的重复数据没有被过滤掉，这就是UNION ALL与UNION的区别。

6.3​ 连接查询

在关系型数据库中，建立数据表时，不必确定各个数据之间的关系，通常将每个实体的所有信息存放在一个表中，当查询数据时，通过连接操作查询多个表中的实体信息，当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询，接下来将详细讲解连接查询的相关内容。

6.4.1​ 创建数据表和表结构的说明

在讲解查询前，首先创建两个数据表并插入数据，用于后面的例题演示，分别为员工表emp和部门表dept，首先了解员工表emp的表结构，如表6.10所示。

表6.10​ emp表

字段	字段类型	说明
empno	int	员工编号
ename	varchar(50)	员工姓名
job	varchar(50)	员工工作
mgr	int	领导编号
hiredate	date	入职日期
sal	decimal(7,2)	月薪
comm	decimal(7,2)	奖金
deptno	int	部门编号

表6.10中列出了员工表的字段、字段类型和说明，接着创建emp表，SQL语句如下所示。

CREATE TABLE emp(

empno INT COMMENT '员工编号',

ename VARCHAR(50) COMMENT '员工姓名',

job VARCHAR(50) COMMENT '员工工作',

mgr INT COMMENT '领导编号',

hiredate DATE COMMENT '入职日期',

sal DECIMAL(7,2) COMMENT '月薪',

comm decimal(7,2) COMMENT '奖金',

deptno INT COMMENT '部门编号'

);

创建完成emp表后，向表中插入数据，SQL语句如下所示。

INSERT INTO emp values

(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);

INSERT INTO emp values

(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);

INSERT INTO emp values

(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);

INSERT INTO emp values

(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);

INSERT INTO emp values

(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);

INSERT INTO emp values

(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);

INSERT INTO emp values

(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);

INSERT INTO emp values

(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);

INSERT INTO emp values

(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

INSERT INTO emp values

(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);

INSERT INTO emp values

(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);

INSERT INTO emp values

(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);

INSERT INTO emp values

(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);

INSERT INTO emp values

(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

然后创建部门表dept，表结构如表6.11所示。

表6.11​ dept表

字段	字段类型	说明
deptno	int	部门编码
dname	varchar(50)	部门名称
loc	varchar(50)	部门所在地点

表6.11中列出了部门表的字段、字段类型和说明，接着创建部门表，SQL语句如下所示。

CREATE TABLE dept(

deptno INT COMMENT '部门编码',

dname varchar(14) COMMENT '部门名称',

loc varchar(13) COMMENT '部门所在地点'

);

创建完成部门表后，向表中插入数据，SQL语句如下所示。

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');

INSERT INTO dept values(30, 'SALES', 'CHICAGO');

INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

至此，两张表创建完成，本章后面的演示例题会用到这两张表。

6.4.2​ 笛卡尔积问题

笛卡尔积在SQL中的实现方式是交叉连接（Cross Join），所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合，接下来通过一个案例演示笛卡尔积问题，这里使用交叉查询来演示，交叉查询的语法格式如下所示。

SELECT 查询字段 FROM 表1 CROSS JOIN 表2;

以上语法格式中，CROSS JOIN用于连接两个要查询的表，用于查询两个表中的所有数据组合。

例6-5​ 查询所有员工编号、员工姓名、部门编号、部门名称，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e CROSS JOIN dept d;

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7369 | SMITH | 10 | ACCOUNTING |

| 7369 | SMITH | 20 | RESEARCH |

| 7369 | SMITH | 30 | SALES |

| 7369 | SMITH | 40 | OPERATIONS |

| 7499 | ALLEN | 10 | ACCOUNTING |

| 7499 | ALLEN | 20 | RESEARCH |

| 7499 | ALLEN | 30 | SALES |

| 7499 | ALLEN | 40 | OPERATIONS |

| 7521 | WARD | 10 | ACCOUNTING |

| 7521 | WARD | 20 | RESEARCH |

| 7521 | WARD | 30 | SALES |

| 7521 | WARD | 40 | OPERATIONS |

| 7566 | JONES | 10 | ACCOUNTING |

| 7566 | JONES | 20 | RESEARCH |

| 7566 | JONES | 30 | SALES |

| 7566 | JONES | 40 | OPERATIONS |

| 7654 | MARTIN | 10 | ACCOUNTING |

| 7654 | MARTIN | 20 | RESEARCH |

| 7654 | MARTIN | 30 | SALES |

| 7654 | MARTIN | 40 | OPERATIONS |

| 7698 | BLAKE | 10 | ACCOUNTING |

| 7698 | BLAKE | 20 | RESEARCH |

| 7698 | BLAKE | 30 | SALES |

| 7698 | BLAKE | 40 | OPERATIONS |

| 7782 | CLARK | 10 | ACCOUNTING |

| 7782 | CLARK | 20 | RESEARCH |

| 7782 | CLARK | 30 | SALES |

| 7782 | CLARK | 40 | OPERATIONS |

| 7788 | SCOTT | 10 | ACCOUNTING |

| 7788 | SCOTT | 20 | RESEARCH |

| 7788 | SCOTT | 30 | SALES |

| 7788 | SCOTT | 40 | OPERATIONS |

| 7839 | KING | 10 | ACCOUNTING |

| 7839 | KING | 20 | RESEARCH |

| 7839 | KING | 30 | SALES |

| 7839 | KING | 40 | OPERATIONS |

| 7844 | TURNER | 10 | ACCOUNTING |

| 7844 | TURNER | 20 | RESEARCH |

| 7844 | TURNER | 30 | SALES |

| 7844 | TURNER | 40 | OPERATIONS |

| 7876 | ADAMS | 10 | ACCOUNTING |

| 7876 | ADAMS | 20 | RESEARCH |

| 7876 | ADAMS | 30 | SALES |

| 7876 | ADAMS | 40 | OPERATIONS |

| 7900 | JAMES | 10 | ACCOUNTING |

| 7900 | JAMES | 20 | RESEARCH |

| 7900 | JAMES | 30 | SALES |

| 7900 | JAMES | 40 | OPERATIONS |

| 7902 | FORD | 10 | ACCOUNTING |

| 7902 | FORD | 20 | RESEARCH |

| 7902 | FORD | 30 | SALES |

| 7902 | FORD | 40 | OPERATIONS |

| 7934 | MILLER | 10 | ACCOUNTING |

| 7934 | MILLER | 20 | RESEARCH |

| 7934 | MILLER | 30 | SALES |

| 7934 | MILLER | 40 | OPERATIONS |

+-------+--------+--------+------------+

56 rows in set (0.00 sec)

从以上执行结果可看出，一共查询出了56条数据，这就是笛卡尔积问题，在实际应用中，笛卡尔积本身大多没有什么实际用处，只有在两个表连接时加上限制条件，才会有实际意义，如例6-5，实际上是需要查询出每个员工及其对应部门的信息，并不需要进行数据的组合，这时就需要加入过滤条件，将不需要的数据过滤掉，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e CROSS JOIN dept d

-> WHERE e.deptno=d.deptno;

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7369 | SMITH | 20 | RESEARCH |

| 7499 | ALLEN | 30 | SALES |

| 7521 | WARD | 30 | SALES |

| 7566 | JONES | 20 | RESEARCH |

| 7654 | MARTIN | 30 | SALES |

| 7698 | BLAKE | 30 | SALES |

| 7782 | CLARK | 10 | ACCOUNTING |

| 7788 | SCOTT | 20 | RESEARCH |

| 7839 | KING | 10 | ACCOUNTING |

| 7844 | TURNER | 30 | SALES |

| 7876 | ADAMS | 20 | RESEARCH |

| 7900 | JAMES | 30 | SALES |

| 7902 | FORD | 20 | RESEARCH |

| 7934 | MILLER | 10 | ACCOUNTING |

+-------+--------+--------+------------+

14 rows in set (0.00 sec)

从以上执行结果可看出，使用交叉查询并加入过滤条件，成功查询出需要的结果数据，一共查询出了14个员工及其对应部门的信息。

6.4.3​ 内连接

内连接的连接查询结果集中仅包含满足条件的行，MySQL中默认的连接方式就是内连接，前面学习了交叉连接的语法，但这种写法并不是SQL标准中的查询方式，可以理解为方言，SQL标准中的内连接语法格式如下所示。

SELECT 查询字段 FROM 表1 [INNER] JOIN 表2

ON 表1.关系字段 = 表2.关系字段 WHERE 查询条件;

以上语法格式中，INNER JOIN用于连接两个表，其中INNER可以省略，因为MySQL默认的连接方式就是内连接，ON用来指定连接条件，类似于WHERE关键字，接下来通过具体案例演示内连接的用法。

例6-6​ 使用SQL标准语法查询所有员工编号、员工姓名、部门编号、部门名称，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e

-> INNER JOIN dept d

-> ON e.deptno=d.deptno;

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7369 | SMITH | 20 | RESEARCH |

| 7499 | ALLEN | 30 | SALES |

| 7521 | WARD | 30 | SALES |

| 7566 | JONES | 20 | RESEARCH |

| 7654 | MARTIN | 30 | SALES |

| 7698 | BLAKE | 30 | SALES |

| 7782 | CLARK | 10 | ACCOUNTING |

| 7788 | SCOTT | 20 | RESEARCH |

| 7839 | KING | 10 | ACCOUNTING |

| 7844 | TURNER | 30 | SALES |

| 7876 | ADAMS | 20 | RESEARCH |

| 7900 | JAMES | 30 | SALES |

| 7902 | FORD | 20 | RESEARCH |

| 7934 | MILLER | 10 | ACCOUNTING |

+-------+--------+--------+------------+

14 rows in set (0.00 sec)

从以上执行结果可看出，使用SQL表中语法进行内连接查询，成功查询出了所有员工及对应部门的信息，SQL语句中为emp表起别名为e，为dept表起别名为d，查询字段和过滤数据时，用“e.”或“d.”代表使用的是哪张表的字段。

例6-7​ 使用SQL标准语法查询员工名字中包含字母A的员工编号、员工姓名、部门编号、部门名称，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e

-> INNER JOIN dept d

-> ON e.deptno=d.deptno

-> WHERE e.ename LIKE '%A%';

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7499 | ALLEN | 30 | SALES |

| 7521 | WARD | 30 | SALES |

| 7654 | MARTIN | 30 | SALES |

| 7698 | BLAKE | 30 | SALES |

| 7782 | CLARK | 10 | ACCOUNTING |

| 7876 | ADAMS | 20 | RESEARCH |

| 7900 | JAMES | 30 | SALES |

+-------+--------+--------+------------+

7 rows in set (0.05 sec)

从以上执行结果可看出，员工名字中包含字母A的员工有七个，查询结果查出了这七个员工及其对应部门的信息。

6.4.4​ 外连接

前面讲解了内连接的查询，返回的结果只包含符合查询条件和连接条件的数据，然而有时还需要包含没有关联的数据，返回查询结果中不仅包含符合条件的数据，还包含左表或右表或两个表中的所有数据，此时就需要用到外连接查询，外连接查询包括左外连接和右外连接，接下来进行详细讲解。

1.左外连接

左外连接是以左表中的数据为基准，若左表有数据且右表没有数据，则显示左表中的数据，右表中的数据显示为空，左外连接的语法格式如下所示。

SELECT 查询字段 FROM 表1 LEFT [OUTER] JOIN 表2

ON 表1.关系字段=表2.关系字段 WHERE 查询条件;

以上语法格式中，LFET JOIN表示返回左表中的所有记录以及右表中符合连接条件的记录，OUTER是可以省略不写的，ON后是两张表的连接条件，WHERE关键字后可以加查询条件，接下来通过具体案例演示左外连接的使用。

例6-8​ 使用左外连接对emp表和dept表进行查询，其中emp为左表，查询所有员工编号、员工姓名、部门编号和部门名称，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e

-> LEFT JOIN dept d

-> ON e.deptno=d.deptno;

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7369 | SMITH | 20 | RESEARCH |

| 7499 | ALLEN | 30 | SALES |

| 7521 | WARD | 30 | SALES |

| 7566 | JONES | 20 | RESEARCH |

| 7654 | MARTIN | 30 | SALES |

| 7698 | BLAKE | 30 | SALES |

| 7782 | CLARK | 10 | ACCOUNTING |

| 7788 | SCOTT | 20 | RESEARCH |

| 7839 | KING | 10 | ACCOUNTING |

| 7844 | TURNER | 30 | SALES |

| 7876 | ADAMS | 20 | RESEARCH |

| 7900 | JAMES | 30 | SALES |

| 7902 | FORD | 20 | RESEARCH |

| 7934 | MILLER | 10 | ACCOUNTING |

+-------+--------+--------+------------+

14 rows in set (0.00 sec)

从以上执行结果可看出，emp表中的所有员工编号和员工姓名都显示出来了，并且把对应的部门编号和部门名称显示了出来，但是dept表中还有部门编号为40的部门并没有显示，这是因为左外连接只显示左表的所有数据和左表需要关联的数据。

2.右外连接

右外连接是以右表中的数据为基准，若右表有数据且左表没有数据，则显示右表中的数据，左表中的数据显示为空，右外连接的语法格式如下所示。

SELECT 查询字段 FROM 表1 RIGHT [OUTER] JOIN 表2

ON 表1.关系字段=表2.关系字段 WHERE 查询条件;

以上语法格式中，RIGHT JOIN表示返回右表中的所有记录以及左表中符合连接条件的记录，OUTER是可以省略不写的，ON后是两张表的连接条件，WHERE关键字后可以加查询条件，接下来通过具体案例演示右外连接的使用。

例6-9​ 使用右外连接对emp表和dept表进行查询，其中dept为右表，查询所有员工编号、员工姓名、部门编号和部门名称，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e

-> RIGHT JOIN dept d

-> ON e.deptno=d.deptno;

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7782 | CLARK | 10 | ACCOUNTING |

| 7839 | KING | 10 | ACCOUNTING |

| 7934 | MILLER | 10 | ACCOUNTING |

| 7369 | SMITH | 20 | RESEARCH |

| 7566 | JONES | 20 | RESEARCH |

| 7788 | SCOTT | 20 | RESEARCH |

| 7876 | ADAMS | 20 | RESEARCH |

| 7902 | FORD | 20 | RESEARCH |

| 7499 | ALLEN | 30 | SALES |

| 7521 | WARD | 30 | SALES |

| 7654 | MARTIN | 30 | SALES |

| 7698 | BLAKE | 30 | SALES |

| 7844 | TURNER | 30 | SALES |

| 7900 | JAMES | 30 | SALES |

| NULL | NULL | 40 | OPERATIONS |

+-------+--------+--------+------------+

15 rows in set (0.00 sec)

从以上执行结果可看出，dept表中的所有部门编号和部门名称都显示出来了，并且把对应的员工信息显示了出来，其中部门编号为40的部门没有对应的员工，员工编号和员工姓名显示为NULL，这是因为右外连接只显示右表的所有数据和右表需要关联的数据。

6.4.5​ 多表连接

前面学习了内连接和外连接，都是两张表之间的连接查询，实际上随着业务的复杂，可能需要连接更多的表，可以是三张、四张甚至更多，但表连接的过多，会严重影响查询效率，所以连接查询一般不会超出七张表的连接，多表连接的语法格式如下所示。

SELECT 查询字段

FROM 表1 [别名]

JOIN 表2 [别名]

ON 表1. 关系字段=表2.关系字段

JOIN 表 m

ON……;

以上语法格式中，因为连接多个表，为了方便书写，通常会给表起别名，当然也可以不起别名，多个表通过JOIN关键字连接，ON关键字后是表与表直接的关系字段，接下来通过具体案例演示多表连接的使用。

例6-10​ 分别创建学生表student、科目表subject和成绩表score，查询出学生编号、学生姓名、科目和对应分数。

首先创建student表，SQL语句如下所示。

mysql> CREATE TABLE student(

-> stu_id INT PRIMARY KEY,

-> stu_name VARCHAR(20)

-> );

Query OK, 0 rows affected (0.07 sec)

以上执行结果证明student表创建完成，向表中添加数据，SQL语句如下所示。

mysql> INSERT INTO student(

-> stu_id,

-> stu_name) VALUES(

-> 1,

-> 'zs'

-> );

Query OK, 1 row affected (0.08 sec)

接着创建subject表，SQL语句如下所示。

mysql> CREATE TABLE subject(

-> sub_id INT PRIMARY KEY,

-> sub_name VARCHAR(20)

-> );

Query OK, 0 rows affected (0.10 sec)

以上执行结果证明subject表创建完成，向表中添加数据，SQL语句如下所示。

mysql> INSERT INTO subject(

-> sub_id,

-> sub_name) VALUES(

-> 1,

-> 'math'

-> );

Query OK, 1 row affected (0.03 sec)

最后创建score表，SQL语句如下所示。

mysql> CREATE TABLE score(

-> sco_id INT PRIMARY KEY,

-> score INT,

-> stu_id INT,

-> sub_id INT

-> );

Query OK, 0 rows affected (0.08 sec)

以上执行结果证明score表创建完成，向表中添加数据，SQL语句如下所示。

mysql> INSERT INTO score(

-> sco_id,

-> score,

-> stu_id,

-> sub_id) VALUES (

-> 1,

-> 80,

-> 1,

-> 1

-> );

Query OK, 1 row affected (0.06 sec)

查询学生编号、学生姓名、科目和对应分数，SQL语句如下所示。

mysql> SELECT s.stu_id,s.stu_name,sj.sub_name,sc.score

-> FROM student s

-> JOIN score sc

-> ON s.stu_id=sc.stu_id

-> JOIN subject sj

-> ON sc.sub_id=sj.sub_id;

+--------+----------+----------+-------+

| stu_id | stu_name | sub_name | score |

+--------+----------+----------+-------+

| 1 | zs | math | 80 |

+--------+----------+----------+-------+

1 row in set (0.00 sec)

从以上执行结果可看出，通过连接student、score和subject三张表，查询出了学生编号、学生姓名、科目和对应分数，这是多表连接查询的基本应用。

6.4.6​ 自然连接

前面学习的表连接查询，需要指定表与表之间的连接字段，SQL标准中还有一种自然连接，不需要指定连接字段，表与表之间列名和数据类型相同的字段，会被自动匹配，自然连接默认按内连接的方式进行查询，自然连接的语法格式如下所示。

SELECT 查询字段

FROM 表1 [别名]

NATURAL JOIN 表2 [别名];

以上语法格式中，通过NATURAL关键字使两张表进行自然连接，默认是按内连接的方式进行查询，接下来通过具体案例演示自然连接的使用。

例6-11​ 使用自然连接查询所有员工编号、员工姓名、部门编号、部门名称，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e

-> NATURAL JOIN dept d;

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7369 | SMITH | 20 | RESEARCH |

| 7499 | ALLEN | 30 | SALES |

| 7521 | WARD | 30 | SALES |

| 7566 | JONES | 20 | RESEARCH |

| 7654 | MARTIN | 30 | SALES |

| 7698 | BLAKE | 30 | SALES |

| 7782 | CLARK | 10 | ACCOUNTING |

| 7788 | SCOTT | 20 | RESEARCH |

| 7839 | KING | 10 | ACCOUNTING |

| 7844 | TURNER | 30 | SALES |

| 7876 | ADAMS | 20 | RESEARCH |

| 7900 | JAMES | 30 | SALES |

| 7902 | FORD | 20 | RESEARCH |

| 7934 | MILLER | 10 | ACCOUNTING |

+-------+--------+--------+------------+

14 rows in set (0.00 sec)

从以上执行结果可看出，通过自然连接，不需要指定连接字段，就会查询出正确的结果，不会出现重复数据，这是自然连接默认的连接查询方式，自然连接也可以指定使用左连接或右连接的方式进行查询，语法格式如下所示。

SELECT 查询字段

FROM 表1 [别名]

NATURAL [LEFT|RIGHT] JOIN 表2 [别名];

以上语法格式中，若需要指定左连接或右连接，只需要添加LEFT或RIGHT关键字即可，接下来通过具体案例演示自然连接左连接查询和右连接查询的使用。

例6-12​ 使用自然连接的左连接查询方式对emp表和dept表进行查询，其中emp为左表，查询所有员工编号、员工姓名、部门编号和部门名称，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e

-> NATURAL LEFT JOIN dept d;

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7369 | SMITH | 20 | RESEARCH |

| 7499 | ALLEN | 30 | SALES |

| 7521 | WARD | 30 | SALES |

| 7566 | JONES | 20 | RESEARCH |

| 7654 | MARTIN | 30 | SALES |

| 7698 | BLAKE | 30 | SALES |

| 7782 | CLARK | 10 | ACCOUNTING |

| 7788 | SCOTT | 20 | RESEARCH |

| 7839 | KING | 10 | ACCOUNTING |

| 7844 | TURNER | 30 | SALES |

| 7876 | ADAMS | 20 | RESEARCH |

| 7900 | JAMES | 30 | SALES |

| 7902 | FORD | 20 | RESEARCH |

| 7934 | MILLER | 10 | ACCOUNTING |

+-------+--------+--------+------------+

14 rows in set (0.02 sec)

从以上执行结果可看出，emp表中的所有员工编号和员工姓名都显示出来了，并且把对应的部门编号和部门名称显示了出来，但是dept表中还有部门编号为40的部门并没有显示，说明自然连接使用了左连接的方式进行查询。

例6-13​ 使用自然连接的右连接查询方式对emp表和dept表进行查询，其中dept为右表，查询所有员工编号、员工姓名、部门编号和部门名称，SQL语句如下所示。

mysql> SELECT e.empno,e.ename,d.deptno,d.dname

-> FROM emp e

-> NATURAL RIGHT JOIN dept d;

+-------+--------+--------+------------+

| empno | ename | deptno | dname |

+-------+--------+--------+------------+

| 7782 | CLARK | 10 | ACCOUNTING |

| 7839 | KING | 10 | ACCOUNTING |

| 7934 | MILLER | 10 | ACCOUNTING |

| 7369 | SMITH | 20 | RESEARCH |

| 7566 | JONES | 20 | RESEARCH |

| 7788 | SCOTT | 20 | RESEARCH |

| 7876 | ADAMS | 20 | RESEARCH |

| 7902 | FORD | 20 | RESEARCH |

| 7499 | ALLEN | 30 | SALES |

| 7521 | WARD | 30 | SALES |

| 7654 | MARTIN | 30 | SALES |

| 7698 | BLAKE | 30 | SALES |

| 7844 | TURNER | 30 | SALES |

| 7900 | JAMES | 30 | SALES |

| NULL | NULL | 40 | OPERATIONS |

+-------+--------+--------+------------+

15 rows in set (0.00 sec)

从以上执行结果可看出，dept表中的所有部门编号和部门名称都显示出来了，并且把对应的员工信息显示了出来，其中部门编号为40的部门没有对应的员工，员工编号和员工姓名显示为NULL，说明自然连接使用了右连接的方式进行查询。

6.4.7​ 自连接

前面讲解了多表连接查询，还有一种很特殊的连接查询，叫做自连接，它连接的两张表是同一张表，通过起别名进行区分，自连接的语法格式如下所示。

SELECT 查询字段

FROM 表名 [别名1]，表名 [别名2]

WHERE 查询条件;

以上语法格式中，通过给表名起多个别名，实现自连接查询，接下来通过具体案例演示自连接的使用。

例6-14​ 查询员工编号为7369的员工姓名、对应经理编号和经理姓名，SQL语句如下所示。

mysql> SELECT e1.empno , e1.ename,e2.mgr,e2.ename

-> FROM emp e1, emp e2

-> WHERE e1.mgr = e2.empno AND e1.empno = 7369;

+-------+-------+------+-------+

| empno | ename | mgr | ename |

+-------+-------+------+-------+

| 7369 | SMITH | 7566 | FORD |

+-------+-------+------+-------+

1 row in set (0.00 sec)

从以上执行结果可看出，通过自连接，查询出了员工编号为7369的员工姓名、对应经理编号和经理姓名，但自连接在实际应用中并不多见。

6.4​ 子查询

子查询就是嵌套查询，即SELECT中包含SELECT，子查询可以在WHERE关键字后作为查询条件，也可以在FROM关键字后作为表来使用，接下来详细讲解子查询的相关内容。

6.4.8​ 子查询作为查询条件

在复杂查询中，子查询往往作为条件来使用，它可以嵌套在一个SELECT语句中，SELECT语句放在WHERE关键字后，执行查询语句时，首先会执行子查询中的语句，然后将返回结果作为外层查询的过滤条件，接下来通过具体案例演示子查询作为查询条件的使用。

例6-15​ 查询所有工资高于JONES的员工信息，SQL语句如下所示。

mysql> SELECT * FROM emp

-> WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');

+-------+-------+-----------+------+------------+---------+------+--------+

| empno | ename | job | mgr | hiredate | sal | comm | deptno |

+-------+-------+-----------+------+------------+---------+------+--------+

| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

+-------+-------+-----------+------+------------+---------+------+--------+

3 rows in set (0.04 sec)

从以上执行结果可看出，有三个员工的工资高于JONES，SQL语句中使用子查询查出了JONES的工资，将结果作为查询条件查出了大于JONES工资的员工信息。

例6-16​ 查询与SCOTT同一个部门的所有员工信息，SQL语句如下所示。

mysql> SELECT * FROM emp

-> WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');

+-------+-------+---------+------+------------+---------+------+--------+

| empno | ename | job | mgr | hiredate | sal | comm | deptno |

+-------+-------+---------+------+------------+---------+------+--------+

| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |

| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |

| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

+-------+-------+---------+------+------------+---------+------+--------+

5 rows in set (0.00 sec)

从以上执行结果可看出，有五个员工与SCOTT在同一个部门，SQL语句中使用子查询查出了SCOTT的部门编号，将结果作为查询条件查出了该部门的所有员工信息。

例6-17​ 查询工资高于30号部门所有人的员工信息，SQL语句如下所示。

mysql> SELECT * FROM emp WHERE

-> sal > ALL (SELECT sal FROM emp WHERE deptno=30);

+-------+-------+-----------+------+------------+---------+------+--------+

| empno | ename | job | mgr | hiredate | sal | comm | deptno |

+-------+-------+-----------+------+------------+---------+------+--------+

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |

| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

+-------+-------+-----------+------+------------+---------+------+--------+

4 rows in set (0.02 sec)

从以上执行结果可看出，有四个员工工资高于30号部门所有人，SQL语句中使用子查询查出了30号部门所有人的工资，将结果作为查询条件进行比较。

例6-18​ 查询工作和工资与MARTIN（马丁）完全相同的员工信息，SQL语句如下所示。

mysql> SELECT * FROM emp WHERE (job,sal) IN

-> (SELECT job,sal FROM emp WHERE ename='MARTIN');

+-------+--------+--------+------+----------+---------+------+--------+

| empno | ename | job | mgr | hiredate | sal | comm | deptno |

+-------+--------+--------+------+----------+---------+------+--------+

| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |

| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |

+-------+--------+--------+------+----------+---------+---------+-----+

2 rows in set (0.02 sec)

从以上执行结果可看出，有两个员工工作和工资与MARTIN（马丁）完全相同，SQL语句中使用子查询查出了MARTIN（马丁）的工作与工资，将结果作为查询条件进行比较。

6.4.9​ 子查询作为表

前面讲解了子查询作为查询条件来使用，子查询还可以作为表来使用，SELECT子句放在FROM关键字后，执行查询语句时，首先会执行子查询中的语句，然后将返回结果作为外层查询的数据源使用，接下来通过具体案例演示子查询作为表的使用。

例6-19​ 查询员工编号为7788的员工名称、员工工资、部门名称、部门地址，SQL语句如下所示。

mysql> SELECT e.ename, e.sal, d.dname, d.loc

-> FROM emp e, (SELECT dname,loc,deptno FROM dept) d

-> WHERE e.deptno=d.deptno AND e.empno=7788;

+-------+---------+----------+--------+

| ename | sal | dname | loc |

+-------+---------+----------+--------+

| SCOTT | 3000.00 | RESEARCH | DALLAS |

+-------+---------+----------+--------+

1 row in set (0.02 sec)

从以上执行结果可看出，编号为7788的员工是SCOTT，查询语句查询出了他的姓名、工资、部门名称和部门地址，SQL语句中使用子查询查询出了所有的部门编号、部门名称和部门地址，然后将返回结果作为外层查询的数据源使用。

6.5​ 本章小结

本章首先介绍了表与表之间的关系，重点讲解了一对一、一对多和多对多，然后介绍了合并结果集的用法，接着讲解了连接查询的用法，最后讲解了子查询，大家需要多练习，不需要死记硬背。

6.6​ 习题

1.思考题

(1) 请简述表与表之间有哪些关系。

(2) 请简述UNION和UNION ALL的区别。

(3) 请简述什么是笛卡尔积问题。

(4) 请简述左外连接和右外连接的区别。

(5) 请简述自然连接如何使用。