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

不念不忘少年蓝@ 2022-06-06 03:42 182阅读 0赞

--------------------

## 前言 ##

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

## **`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)