前言
在使用子查询语句的过程中经常使用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)