ANY、SOME关键字

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

mysql> create table nums(
    -> num int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into nums values (2), (4), (5), (1), (7);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from nums;
+------+
| num  |
+------+
|    2 |
|    4 |
|    5 |
|    1 |
|    7 |
+------+
5 rows in set (0.00 sec)

mysql> create table nums2(
    -> num int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into nums2 values (2), (9), (5), (8), (7);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from nums2;
+------+
| num  |
+------+
|    2 |
|    9 |
|    5 |
|    8 |
|    7 |
+------+
5 rows in set (0.00 sec)

mysql> select num from nums where num < any(select num from nums2);
+------+
| num  |
+------+
|    2 |
|    4 |
|    5 |
|    1 |
|    7 |
+------+
5 rows in set (0.00 sec)

mysql> select num from nums where num < some(select num from nums2);
+------+
| num  |
+------+
|    2 |
|    4 |
|    5 |
|    1 |
|    7 |
+------+
5 rows in set (0.00 sec)

mysql> select num from nums where num > some(select num from nums2);
+------+
| num  |
+------+
|    4 |
|    5 |
|    7 |
+------+
3 rows in set (0.00 sec)

ALL关键字

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。

mysql> select num from nums where num < all(select num from nums2);
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> select num from nums where num > all(select num from nums2);
Empty set (0.00 sec)

EXISTS关键字

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

mysql> select * from user where exists(select id from orders where id=3);
Empty set (0.00 sec)

mysql> select * from user where exists(select id from orders where id=2);
+----+-------+----------+--------+
| id | name  | order_id | sex    |
+----+-------+----------+--------+
|  1 | ronie |        1 | male   |
|  2 | kelly |        2 | female |
|  3 | mike  |     NULL | male   |
+----+-------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from user where not exists(select id from orders where id=3);
+----+-------+----------+--------+
| id | name  | order_id | sex    |
+----+-------+----------+--------+
|  1 | ronie |        1 | male   |
|  2 | kelly |        2 | female |
|  3 | mike  |     NULL | male   |
+----+-------+----------+--------+
3 rows in set (0.00 sec)

IN关键字

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

mysql> select * from user where order_id in (select id from orders);
+----+-------+----------+--------+
| id | name  | order_id | sex    |
+----+-------+----------+--------+
|  1 | ronie |        1 | male   |
|  2 | kelly |        2 | female |
+----+-------+----------+--------+
2 rows in set (0.00 sec)