mysql中 any、some、exist、not exist关键字的解释以及运用

mysql中 any、some、exist、not exist关键字的解释以及运用

本文主要是对mysql中比较易混的几个关键字的解释和运用来说明的

• any 或 some
  any 或 some关键字是同义词，表示满足其中任何一个条件即可，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件
  举例：
  创建两个表并插入数据

create table tal1 (num1 int not null);
create table tal2 (num2 int not null);
insert into tal1 values (1),(5),(13),(27);
insert into tal2 values (6),(14),(11),(20);

mysql> select num1 from tal1 where num1 > ANY (select num2 from tal2);
+------+
| num1 |
+------+
| 13   |
| 27   |
+------+
2 rows in set (0.01 sec)

mysql> select num1 from tal1 where num1 > some (select num2 from tal2);
+------+
| num1 |
+------+
| 13   |
| 27   |
+------+
2 rows in set (0.02 sec)

• exists 和 not exists
  exists关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么exists的结果就为true，此时外层的查询语句将进行查询；如果子查询没有返回任何行，那么exists返回的结果是false，此时外层语句将不进行查询。
  举例：
  创建两个表并插入数据

create table tal1 (num1 int not null);
create table tal2 (num2 int not null);
insert into tal1 values (1),(5),(13),(27);
insert into tal2 values (6),(14),(11),(20);

mysql> select num1 from tal1 where exists (select num2 from tal2);
+------+
| num1 |
+------+
| 1    |
| 5    |
| 13   |
| 27   |
+------+
4 rows in set (0.01 sec)

mysql> select num1 from tal1 where exists (select count(1) from tal2);
+------+
| num1 |
+------+
| 1    |
| 5    |
| 13   |
| 27   |
+------+
4 rows in set (0.01 sec)

mysql> select num1 from tal1 where exists (select num2 from tal2 where num2>100 );
Empty set (0.00 sec)

从上面代码看出 exists后面的子查询查询的什么数据并不重要，重要的是要返回数据
not exists 与exists的功能相反
举例：

mysql> select num1 from tal1 where exists (select num2 from tal2 where num2>100 );
Empty set (0.00 sec)

mysql> select num1 from tal1 where not exists (select num2 from tal2);
Empty set (0.00 sec)

mysql> select num1 from tal1 where not exists (select count(1) from tal2);
Empty set (0.00 sec)

mysql> select num1 from tal1 where not exists (select num2 from tal2 where num2>100 );
+------+
| num1 |
+------+
| 1    |
| 5    |
| 13   |
| 27   |
+------+
4 rows in set (0.01 sec)