MySQL中的多表操作

之前章节对数据的操作都是基于一张数据表完成的，即单表操作，然而实际应用中业务逻辑较为复杂，表与表之间可能存在业务联系，有时需要基于两张或两张以上的数据表进行操作，即多表操作。本章将针对多表操作的相关知识进行讲解。

多表查询

在关系数据库中，一张数据表通常存储一个实体的信息。当两张或多张数据表中存在相同意义的字段时，如果需要同时显示多张数据表中的数据，便可以通过这些意义相同的字段将不同的数据表进行连接并对连接后的数据表进行查询，这样的查询通常称为连接查询。在MySQL中，连接查询包括交叉连接查询、内连接查询、外连接查询、复合条件连接查询，本节将对这些连接查询进行讲解。

交叉连接查询

交叉连接(CROSS JOIN)查询返回的结果是被连接的两张数据表中所有数据行的笛卡儿积。例如，数据库ems中的部门表dept有3条部门记录，员工表emp有14条员工记录，如果对这两张数据表进行交叉连接查询，那么交叉连接查询后的笛卡儿积就有42（3*14）条记录。

交叉链接的语法格式如下：

SELECT 字段名 FROM 数据表名1 CROSS JOIN 数据表名2;

或

SELECT 字段名 FROM 数据表名1 数据表名2;

在上述语法格式中，两种语法格式的返回结果相同，其中字段名指的是需要查询的字段名称；数据表名1和数据表名2指的是需要交叉链接的数据表的名称；CROSS JOIN用于链接要查询的数据表，通过CROSS JOIN语句可以查询两个表中所有的数据组合。

下面通过一个案例演示交叉链接查询。

例如，我们想要通过SQL语句对数据库ems中员工表emp和部门表dept进行交叉茶查询，具体如下。

先创建一个员工表：

mysql> CREATE TABLE emp(
    -> empno INT PRIMARY KEY,
    -> ename VARCHAR(20) UNIQUE NOT NULL,
    -> job VARCHAR(20) NOT NULL,
    -> mgr INT,
    -> sal DECIMAL(7,2),
    -> comm DECIMAL(7,2),
    -> deptno INT
    -> );
Query OK, 0 rows affected (0.03 sec)

创建员工表使用的数据类型大家应该都认识，可能DECIMAL这个大家不太熟悉，我带大家回忆一下，DECIMAL是属于整数类型中的定点数类型，定点数类型就是表示精度确定的小数类型，适合用于对进度要求比较高的数据。

里面的参数7是整数和小数位的有效位数，一共是七位，2是小数位数，表示小数位有效位数为两位。

再插入数据：

mysql> INSERT INTO emp VALUES
    -> (994,'孙七','销售',9698,2500,300,30),
    -> (952,'周八','销售',9698,2250,500,30),
    -> (936,'吴九','销售',9698,2250,1000,30),
    -> (935,'陈十一','经理',9839,3500,NULL,10),
    -> (911,'王五','分析员',9866,4000,NULL,20),
    -> (951,'郑十','销售',9698,2500,0,30),
    -> (991,'赵六','分析员',9566,4000.00,NULL,20),
    -> (969,'萧二','保洁',9698,2000,NULL,30);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

员工表创建好了，接下来就是部门表了

mysql> CREATE TABLE dept(
    -> deptno INT PRIMARY KEY,
    -> dname VARCHAR(20) UNIQUE
    -> );
Query OK, 0 rows affected (0.02 sec)

然后再插入数据：

mysql> INSERT INTO dept VALUES
    -> (10,'裁决室'),
    -> (20,'研究院'),
    -> (30,'销售部'),
    -> (40,'运营部');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

接着对员工表和部门表进行交叉链接查询，具体SQL语句及执行结果如下。

mysql> SELECT * FROM emp CROSS JOIN dept;
+-------+-----------+-----------+------+---------+---------+--------+--------+-----------+
| empno | ename     | job       | mgr  | sal     | comm    | deptno | deptno | dname     |
+-------+-----------+-----------+------+---------+---------+--------+--------+-----------+
|   911 | 王五      | 分析员    | 9866 | 4000.00 |    NULL |     20 |     30 | 销售部    |
|   911 | 王五      | 分析员    | 9866 | 4000.00 |    NULL |     20 |     40 | 运营部    |
|   911 | 王五      | 分析员    | 9866 | 4000.00 |    NULL |     20 |     10 | 裁决室    |
|   911 | 王五      | 分析员    | 9866 | 4000.00 |    NULL |     20 |     20 | 研究院    |
|   935 | 陈十一    | 经理      | 9839 | 3500.00 |    NULL |     10 |     30 | 销售部    |
|   935 | 陈十一    | 经理      | 9839 | 3500.00 |    NULL |     10 |     40 | 运营部    |
|   935 | 陈十一    | 经理      | 9839 | 3500.00 |    NULL |     10 |     10 | 裁决室    |
|   935 | 陈十一    | 经理      | 9839 | 3500.00 |    NULL |     10 |     20 | 研究院    |
|   936 | 吴九      | 销售      | 9698 | 2250.00 | 1000.00 |     30 |     30 | 销售部    |
|   936 | 吴九      | 销售      | 9698 | 2250.00 | 1000.00 |     30 |     40 | 运营部    |
|   936 | 吴九      | 销售      | 9698 | 2250.00 | 1000.00 |     30 |     10 | 裁决室    |
|   936 | 吴九      | 销售      | 9698 | 2250.00 | 1000.00 |     30 |     20 | 研究院    |
|   951 | 郑十      | 销售      | 9698 | 2500.00 |    0.00 |     30 |     30 | 销售部    |
|   951 | 郑十      | 销售      | 9698 | 2500.00 |    0.00 |     30 |     40 | 运营部    |
|   951 | 郑十      | 销售      | 9698 | 2500.00 |    0.00 |     30 |     10 | 裁决室    |
|   951 | 郑十      | 销售      | 9698 | 2500.00 |    0.00 |     30 |     20 | 研究院    |
|   952 | 周八      | 销售      | 9698 | 2250.00 |  500.00 |     30 |     30 | 销售部    |
|   952 | 周八      | 销售      | 9698 | 2250.00 |  500.00 |     30 |     40 | 运营部    |
|   952 | 周八      | 销售      | 9698 | 2250.00 |  500.00 |     30 |     10 | 裁决室    |
|   952 | 周八      | 销售      | 9698 | 2250.00 |  500.00 |     30 |     20 | 研究院    |
|   969 | 萧二      | 保洁      | 9698 | 2000.00 |    NULL |     30 |     30 | 销售部    |
|   969 | 萧二      | 保洁      | 9698 | 2000.00 |    NULL |     30 |     40 | 运营部    |
|   969 | 萧二      | 保洁      | 9698 | 2000.00 |    NULL |     30 |     10 | 裁决室    |
|   969 | 萧二      | 保洁      | 9698 | 2000.00 |    NULL |     30 |     20 | 研究院    |
|   985 | 刘一      | 董事长    | NULL | 6000.00 |    NULL |     10 |     30 | 销售部    |
|   985 | 刘一      | 董事长    | NULL | 6000.00 |    NULL |     10 |     40 | 运营部    |
|   985 | 刘一      | 董事长    | NULL | 6000.00 |    NULL |     10 |     10 | 裁决室    |
|   985 | 刘一      | 董事长    | NULL | 6000.00 |    NULL |     10 |     20 | 研究院    |
|   991 | 赵六      | 分析员    | 9566 | 4000.00 |    NULL |     20 |     30 | 销售部    |
|   991 | 赵六      | 分析员    | 9566 | 4000.00 |    NULL |     20 |     40 | 运营部    |
|   991 | 赵六      | 分析员    | 9566 | 4000.00 |    NULL |     20 |     10 | 裁决室    |
|   991 | 赵六      | 分析员    | 9566 | 4000.00 |    NULL |     20 |     20 | 研究院    |
|   994 | 孙七      | 销售      | 9698 | 2500.00 |  300.00 |     30 |     30 | 销售部    |
|   994 | 孙七      | 销售      | 9698 | 2500.00 |  300.00 |     30 |     40 | 运营部    |
|   994 | 孙七      | 销售      | 9698 | 2500.00 |  300.00 |     30 |     10 | 裁决室    |
|   994 | 孙七      | 销售      | 9698 | 2500.00 |  300.00 |     30 |     20 | 研究院    |
+-------+-----------+-----------+------+---------+---------+--------+--------+-----------+
36 rows in set (0.00 sec)

从上述执行结果可以看出，交又连接查询的结果就是两个连接表中所有数据的组合，查询出的记录数为36，即员工表emp的记录数9乘以部门表dept的记录数4；查询出的字段数为9，即员工表emp 的字段数7加上部门表dept的字段数2。由于交又连接查询的结果中存在很多不合理的数据，因此在实际应用中应避免交又连接查询，而是使用具体的条件对数据进行有目的的查询。

内连接查询

内连接(INNER JOIN)查询又称简单连接查询或自然连接查询，是常见的连接查询。内连接查询根据连接条件可以对交又连接查询的部分结果进行筛选，仅筛选出两张表中相互匹配的记录。

内连接在询的语法格式如下。

SELECT 查询字段 FROM 数据表1 [INNER] JOIN 数据表 2 ON 匹配条件；

在上述语法格式中，INNER JOIN用于连接两张数据表，其中INNER 可以省略；ON用于指定查询的匹配条件，即同时匹配两张数据表的条件。由于内连接查询是对两张数据表进行操作，因此需要在匹配条件中指定所操作的字段来源于哪一张数据表，如果为数据表设置了别名，也可以通过别名指定数据表。

例如，技术人员想要通过 SQL语句查询已经分配了部门（部门号不为NULL)的员工的信息，员工信息只需要显示员工姓名和对应部门的名称，具体 SQL 语句及执行结果如下

mysql> SELECT ename,dname FROM emp e JOIN dept d ON e.deptno=d.deptno;
+-----------+-----------+
| ename     | dname     |
+-----------+-----------+
| 王五      | 研究院    |
| 陈十一    | 裁决室    |
| 吴九      | 销售部    |
| 郑十      | 销售部    |
| 周八      | 销售部    |
| 萧二      | 销售部    |
| 刘一      | 裁决室    |
| 赵六      | 研究院    |
| 孙七      | 销售部    |
+-----------+-----------+
9 rows in set (0.00 sec)

在上述查询语句中，通过匹配员工表emp和部门表dept中的字段deptno，使用内连接查询分配了部门的员工信息；由执行结果可知，查询出了员工姓名和对应部门的名称。

一般有关联的两个表肯定是有字段相同的，反之也是，就像学校里的学生的学号，是属于学校的，也是属于学生的，且是唯一的，因此学生表和学院表通过学生的学号id进行关联。这个学号字段就是关键字段，就像我们举例的部门表和员工表，deptno这个部门编号字段就是关键字段，是这两张表链接的桥梁。

自连接

如果在一个连接查询中，涉及的两张数据表是同一张数据表，则这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的数据表在物理上为同一张数据表，但逻辑上分为两张数据表。

例如，技术人员想要通过SQL语句查询员工王五所在部门的所有员工信息。查询时可以使用自连接查询实现，具体SQL语句及执行结果如下。

mysql> SELECT e1.* FROM emp e1 JOIN emp e2 ON e1.deptno=e2.deptno WHERE e2.ename='王五';
+-------+--------+-----------+------+---------+------+--------+
| empno | ename  | job       | mgr  | sal     | comm | deptno |
+-------+--------+-----------+------+---------+------+--------+
|   911 | 王五   | 分析员    | 9866 | 4000.00 | NULL |     20 |
|   991 | 赵六   | 分析员    | 9566 | 4000.00 | NULL |     20 |
+-------+--------+-----------+------+---------+------+--------+
2 rows in set (0.00 sec)

在上述查询语句中，名称为e1和名称为e2的数据表在物理上是同一张数据表e1和e2通过字段deptno进行关联，并且通过WHERE指定筛选的条件：执行结果中，返回了王五所在部门的所有员工信息。由执行结果可知， 王五所在部门有2个员工，分别是赵六和王五。

外连接查询

内连接的查询结果是符合连接条件的记录，然而有时在查询的时，除了要查询出符合条件的数据外，还需要查询出其中一张数据表中符合条件之外的其他数据，此时就需要使用外连接查询。

外连接查询语法格式如下：

SELECT 要查找的字段 FROM 数据表1 LEFT | RIGHT [OUTER] JOIN 数据表2 ON 匹配条件

外连接查询分为左连接(LEFT JOIN)查询和右连接(RIGHT JOIN)查询，一般上述语法格式中的数据表1被称为左表，数据表2被称为右表。使用左连接查询和右连接查询的区别如下。

LEFT JOIN:返回左表中的所有记录和右表中符合连接条件的记录。

RIGHT JOIN:返回右表中的所有记录和左表中符合连接条件的记录。

为了更好地理解外连接查询，下面分别对左连接查询和右连接查询进行讲解。

1.左连接查询

左连接查询的结果包括LEFT JOIN子句中左表的所有记录以及右表中满足连接条件的记录。如果左表的某条记录在右表中不存在，则右表中对应字段的值显示为NULL。

例如，我们想要通过SQL语句查询所有部门名称及部门对应员工的姓名。因为需要查询出所有部门的名称，所以查询时可以使用左连接查询，将部门表作为查询中的左表，具体SQL语句及执行结果如下。

mysql> SELECT d.dname,e.ename FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;
+-----------+-----------+
| dname     | ename     |
+-----------+-----------+
| 研究院    | 赵六      |
| 研究院    | 王五      |
| 裁决室    | 刘一      |
| 裁决室    | 陈十一    |
| 运营部    | NULL      |
| 销售部    | 孙七      |
| 销售部    | 萧二      |
| 销售部    | 周八      |
| 销售部    | 郑十      |
| 销售部    | 吴九      |
+-----------+-----------+
10 rows in set (0.01 sec)

在上述查询语句中，使用左连接将部门表和员工表通过deptno字段进行连接；由执行结果可知，上述查询语句返回了10条记录，其中返回了左表dept中dname字段所有的数据，运营部没有员工，对应的员工姓名字段显示为NULL。

2.右连接查询

右连接查询的结果包括RIGHT JOIN子句中右表的所有记录以及左表中满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表中对应字段的值显示为NULL。

对应字段的值显示为NULL。

例如，我们想要通过SQL语句查询所有员工姓名及对应部门的名称，没有分配部门的员工也需要查询出来。因为需要查询出所有员工的名称，所以查询时可以使用右连接查询，将员工表作为查询中的右表，具体SQL语句及执行结果如下。

mysql> SELECT e.ename,d.dname FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;
+-----------+-----------+
| ename     | dname     |
+-----------+-----------+
| 王五      | 研究院    |
| 陈十一    | 裁决室    |
| 吴九      | 销售部    |
| 郑十      | 销售部    |
| 周八      | 销售部    |
| 萧二      | 销售部    |
| 刘一      | 裁决室    |
| 赵六      | 研究院    |
| 孙七      | 销售部    |
+-----------+-----------+
9 rows in set (0.00 sec)

上还查询语句使用右连接将 dept表和emp表通过deptno字段进行连接。由执行结果可知，返回了九条数据，因为我们使用的是右连接，右边的表是员工表，返回的是员工表中所有的数据，员工表没有员工在运营部，员工的数据也是九条，因此这里返回的数据也是九条。

在这里提醒大家一下，所谓的左连接与右连接不是绝对的，什么意思呢，就是你假如使用左连接(dept LEFT JOIN emp)左边是部门表，因此会返回部门表的所有数据，和emp RIGHT JOIN dept右连接的作用是一样的，因为右连接的右边也是部门表，也会返回部门表的所有数据。因此说左连接与右连接不是绝对的。但左连接与右链接绝对的是写法LEFT JOIN 只会对左边的表起作用，RIGHT JOIN只会对右边的表起作用。

复合条件链接查询

复合条件连接查询是指在连接查询的过程中通过添加过滤条件限制执行结果，使执行结果更精确。

例如，我们想要通过SQL语句查询所有员工的信息，员工信息包含员工所在部门的名称，并且按员工的工资降序排序。在查询时，可以根据deptno字段使用左连接将部门表和员工表进行关联查询，并且使用ORDER BY根据sal字段的值对查询结果进行排序，具体SQL语句及执行结果如下、

mysql> SELECT e.*,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno ORDER BY e.sal DESC;
+-------+-----------+-----------+------+---------+---------+--------+-----------+
| empno | ename     | job       | mgr  | sal     | comm    | deptno | dname     |
+-------+-----------+-----------+------+---------+---------+--------+-----------+
|   985 | 刘一      | 董事长    | NULL | 6000.00 |    NULL |     10 | 裁决室    |
|   911 | 王五      | 分析员    | 9866 | 4000.00 |    NULL |     20 | 研究院    |
|   991 | 赵六      | 分析员    | 9566 | 4000.00 |    NULL |     20 | 研究院    |
|   935 | 陈十一    | 经理      | 9839 | 3500.00 |    NULL |     10 | 裁决室    |
|   951 | 郑十      | 销售      | 9698 | 2500.00 |    0.00 |     30 | 销售部    |
|   994 | 孙七      | 销售      | 9698 | 2500.00 |  300.00 |     30 | 销售部    |
|   936 | 吴九      | 销售      | 9698 | 2250.00 | 1000.00 |     30 | 销售部    |
|   952 | 周八      | 销售      | 9698 | 2250.00 |  500.00 |     30 | 销售部    |
|   969 | 萧二      | 保洁      | 9698 | 2000.00 |    NULL |     30 | 销售部    |
+-------+-----------+-----------+------+---------+---------+--------+-----------+
9 rows in set (0.00 sec)

从执行结果来看，使用复合条件查询的结果更精确，更符合实际要求。

子查询

子查询是指一个查询语句嵌套在另一个语句内部的查询，当某个语句执行所需的过滤条件是另一个SELECT语句的结果时，可以使用子查询。子查询通常在WHERE子句中结合操作符一起使用，操作符可以是IN、EXISTS、ANY、ALL、比较运算符。下面将对结合这几种操作符的子查询进行讲解。

IN关键字结合子查询

IN关键字在单表查询有讲过，忘了的或者不了解的可以去看看

IN关键字结合子查询使用时，需要内层子查询语句返回的结果是一个数据列，这个数据列中的值供外层语句进行比较操作。也可以理解为里面的嵌套查询语句的结果必须是一个数据，只有是数据了才能够继续当做判断条件。

下面通过一个案例演示查询语句中IN关键字结合子查询的使用。

例如，我们想要通过SQL语句查询工资大于2900的员工所属部门。查询时可以先通过子查询返回工资大于2900的员工所在部门的编号，接着使用IN关键字根据部门编号查询部门信息，具体SQL语句及执行结果如下所示。

mysql> SELECT * FROM dept WHERE deptno IN(SELECT deptno FROM  emp WHERE sal>2900);
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     20 | 研究院    |
|     10 | 裁决室    |
+--------+-----------+
2 rows in set (0.01 sec)

从执行结果可知，只有裁决室和研究院存在工资大于2900的员工。

外层SELECT语句使用NOT IN关键字结合子查询使用时，其作用正好和使用相反。例如，技术人员想要通过SQL语句查询工资小于2900的员工所在的部门信息，具体 SQL语句及执行结果如下。

mysql> SELECT * FROM dept WHERE deptno NOT IN(SELECT deptno FROM  emp WHERE sal>2900);
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     40 | 运营部    |
|     30 | 销售部    |
+--------+-----------+
2 rows in set (0.00 sec)

从上述执行结果可以得出，使用NOT IN的查询结果是数据表中使用IN查询到的结果之外的其他数据，只有运营部和销售部不存在工资大于2900的员工。运营部没有人，当然就没有人的工资大于2900了。

EXISTS关键字结合子查询

EXISTS关键字用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回TRUE，否则返回FALSE。使用EXISTS关键字结合子查询进行查询时，会先执行外层查询语句，再根据EXISTS关键字后面子查询的查询结果，判断是否保留外层语句查询出的记录。EXISTS的判断结果为TRUE时，保留对应的记录，否则去除记录。

下面通过一个案例演示查询语句中EXISTS关键字结合子查询的使用。

例如，我们想要通过S0L语句查询工资大于2900的员工所在的部门信息。首先在询出部门的所有信息，然后通过子查的筛选出工资大于2900的员工信息，接着使用EXISTS关键字将符合子查询结果的记录返回：具体SQL语句及执行结果如下

mysql> SELECT * FROM dept WHERE EXISTS(SELECT * FROM emp e WHERE e.deptno =dept.deptno
AND e.sal>2900);
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     20 | 研究院    |
|     10 | 裁决室    |
+--------+-----------+
2 rows in set (0.00 sec)

从上述执行结果可以得出，只有裁决室和研究院存在工资大于2900的员工。

需要注意的是使用EXISTS关键字结合子查询和使用IN关键字结合子查询的结果一致，但在表数据量不同时，这两种方式的性能也不同。当外表数据量比较大而内表数据量比较小时，适合使用IN关键字结合子查询进行查询；当外表数据量比较小而内表数据量比较大时，适合使用EXISTS关键字结合子查询进行查询。

ANY关键字结合子查询

ANY关键字表示“任意一个”的意思，必须和比较操作符一起使用，例如ANY和>结合起来使用表示大于任意一个。ANY关键字结合子查询使用时，表示子查询的查询结果集中的任一查询结果，例如“值1>ANY(子查询)”比较值1是否大于子查询返回的结果集中的任意一个结果。

下面通过一个案例演示查询语句中ANY关键字结合子查询的使用。

例如，我们想要通过SQL语句查询部门编号为10的员工信息，要求查询到的员工信息中工资都高于部门编号为20的部门中的最低工资。查询时可以先使用子查询语句查询出部门编号为20的部门中所有员工工资，接着查询部门编号为10的部门中所有员工信息，最后使用ANY连接两者的工资进行比较。具体SQL语句及执行结果如下。

mysql> SELECT * FROM emp WHERE deptno=10 AND sal>ANY(SELECT sal FROM emp WHERE deptno=30);
+-------+-----------+-----------+------+---------+------+--------+
| empno | ename     | job       | mgr  | sal     | comm | deptno |
+-------+-----------+-----------+------+---------+------+--------+
|   935 | 陈十一    | 经理      | 9839 | 3500.00 | NULL |     10 |
|   985 | 刘一      | 董事长    | NULL | 6000.00 | NULL |     10 |
+-------+-----------+-----------+------+---------+------+--------+
2 rows in set (0.00 sec)

我们可以先看一下emp数据表的数据：

mysql> SELECT * FROM emp;
+-------+-----------+-----------+------+---------+---------+--------+
| empno | ename     | job       | mgr  | sal     | comm    | deptno |
+-------+-----------+-----------+------+---------+---------+--------+
|   911 | 王五      | 分析员    | 9866 | 4000.00 |    NULL |     20 |
|   935 | 陈十一    | 经理      | 9839 | 3500.00 |    NULL |     10 |
|   936 | 吴九      | 销售      | 9698 | 2250.00 | 1000.00 |     30 |
|   951 | 郑十      | 销售      | 9698 | 2500.00 |    0.00 |     30 |
|   952 | 周八      | 销售      | 9698 | 2250.00 |  500.00 |     30 |
|   969 | 萧二      | 保洁      | 9698 | 2000.00 |    NULL |     30 |
|   985 | 刘一      | 董事长    | NULL | 6000.00 |    NULL |     10 |
|   991 | 赵六      | 分析员    | 9566 | 4000.00 |    NULL |     20 |
|   994 | 孙七      | 销售      | 9698 | 2500.00 |  300.00 |     30 |
+-------+-----------+-----------+------+---------+---------+--------+
9 rows in set (0.00 sec)

可以看到在10部门的只有刘一和陈十一。且工资也都是大于在20部门的员工的。

ALL关键字结合子查询

ALL关键字表示“所有”的意思，该关键字结合子查询使用时，表示子查询结果集中的所有结果，例如“值1>ALL (子查询)”比较值1是否大于子查询返回的结果集中的所有结果。

下面通过一个案例演示查询语句中ALL关键字结合子查询的使用。

例如，我们想要通过SQL语句查询部门编号为10的员工信息，要求查询到的员工信息中工资都高于部门编号为20的部门中的最高工资。查询时可以使用子查询将部门编号为20的所有员工工资查询出来，然后将部门编号为10的所有员工工资与子查询的结果进行比较，只要大于子查询中的所有值，就是符合查询条件的记录。这个例子和上面的例子一样，不过使用的关键字不一样，区别在于any代表任何一个，只要有一个符合要求就可以了，不论大小，而all就是所有了，要考虑所有的结果。具体SQL语句及执行结果如下。

mysql> SELECT * FROM emp WHERE deptno=10 AND sal>ALL(SELECT sal FROM emp WHERE deptno=30);
+-------+-----------+-----------+------+---------+------+--------+
| empno | ename     | job       | mgr  | sal     | comm | deptno |
+-------+-----------+-----------+------+---------+------+--------+
|   935 | 陈十一    | 经理      | 9839 | 3500.00 | NULL |     10 |
|   985 | 刘一      | 董事长    | NULL | 6000.00 | NULL |     10 |
+-------+-----------+-----------+------+---------+------+--------+
2 rows in set (0.00 sec)

比较运算符结合子查询

前面讲解的ANY关键字和ALL关键字的子查询中使用了比较运算符。除了>运算符，子查询中还可以使用其他的比较运算符，如<、=、!=等。

下面通过一个案例演示查询语句中比较运算符结合子查询的使用。

例如，我们想要通过SQL语句查询与王五职位相同的员工信息。查询时可以先使用子查询获取王五的职位，接着根据子查询的结果筛选出职位和王五相同的员工信息，具体SQL语句及执行结果如下。

mysql> SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='王五');
+-------+--------+-----------+------+---------+------+--------+
| empno | ename  | job       | mgr  | sal     | comm | deptno |
+-------+--------+-----------+------+---------+------+--------+
|   911 | 王五   | 分析员    | 9866 | 4000.00 | NULL |     20 |
|   991 | 赵六   | 分析员    | 9566 | 4000.00 | NULL |     20 |
+-------+--------+-----------+------+---------+------+--------+
2 rows in set (0.00 sec)

从执行结果来看，王五的职位是分析员，和王五职位相同的员工只有赵六。

一般情况下，表连接查询都可以用子查询替换，但反过来却不一定适用。子查询相对比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接查询更适合查看连接表的数据。

下篇文章是外键约束，点个关注不迷路。