MySQL学习笔记（三）：子查询语句中ANY、SOME、ALL关键字的使用

---

前言

在使用子查询语句的过程中经常使用ANY、SOME、ALL（另外还有一个IN）关键字作为筛选条件。

ANY、SOME、ALL关键字的使用

使用关系表一览

运算符\关键字	ANY	SOME	ALL
> 、>=	最小值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、!=			任意值

操作示例

这里先创建一张数据表并插入一些数据用来作为演示：

mysql> CREATE TABLE salary_table(
    -> id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> position VARCHAR(40) NOT NULL,
    -> salary INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT salary_table(position,salary) VALUES('JAVA',8000),('Java',8400),('Java',9000),('Python',6500),('Python',10000),('Python',8900);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM salary_table;
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  1 | JAVA     |   8000 |
|  2 | Java     |   8400 |
|  3 | Java     |   9000 |
|  4 | Python   |   6500 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
6 rows in set (0.00 sec)

① SOME和ANY的使用

下面语句查找出高于Python职位的其它职位，设定关键字为ANY\ SOME；

mysql> SELECT * FROM salary_table WHERE salary > ANY( SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  1 | JAVA     |   8000 |
|  2 | Java     |   8400 |
|  3 | Java     |   9000 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
5 rows in set (0.02 sec)

mysql> SELECT * FROM salary_table WHERE salary > SOME( SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  1 | JAVA     |   8000 |
|  2 | Java     |   8400 |
|  3 | Java     |   9000 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
5 rows in set (0.00 sec)

当查找关键字为SOME或ANY时，根据查询结果可以得出只要满足设定条件的最小值均可返回结果，与上关系表一致。在这个案例中只要所有salary大于Python职位中最低的6500，该字段就会被返回。
另外可以得出的是使用ANY和SOME关键字的时候功能是一致的，在实际运用中使用ANY偏多一些，有的资料中说SOME是ANY的别名。

② ALL的使用

下面语句查找出高于Python职位的其它职位，设定关键字为ALL；

mysql> SELECT * FROM salary_table WHERE salary > ALL( SELECT salary FROM salary_table WHERE position = 'Java');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  5 | Python   |  10000 |
+----+----------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM salary_table WHERE salary < ALL( SELECT salary FROM salary_table WHERE position = 'Java');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  4 | Python   |   6500 |
+----+----------+--------+
1 row in set (0.00 sec)

当查询关键字设定为ALL时，即所有的条目均要同时满足设定条件。在上述查询中，使用>时查找出大于Java中salary的最大值，而使用<时查找出小于Java中salary的最小值，总结的信息如上关系表一致。

③ =ANY 或 =SOME 等价于IN

IN：在指定项内，同 IN(项1,项2,…)，IN关键字使用之前是需要提供一个列表的，而=ANY和=SOME则正好是筛选数据，组成新的满足条件的列表。
这样我们可以很好理解到和=ANY或=SOME之间的等价关系。

mysql> SELECT * FROM salary_table WHERE salary IN (SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  4 | Python   |   6500 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM salary_table WHERE salary = SOME (SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  4 | Python   |   6500 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM salary_table WHERE salary = ANY (SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  4 | Python   |   6500 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
3 rows in set (0.00 sec)