参考表:employee

参考表:department

ANY关键字表示满足其中任一条件



mysql<span>&gt;</span> <span>SELECT</span> <span>*</span> <span>FROM</span><span> employee
</span><span>-&gt;</span> <span>WHERE</span> d_id<span>!=ANY</span>
<span>-&gt;</span> (<span>SELECT</span> d_id <span>FROM</span><span> department);
</span><span>+</span><span>–</span><span>—-+——+——–+——+——+——————–+</span>
<span>|</span> num <span>|</span> d_id <span>|</span> name <span>|</span> age <span>|</span> sex <span>|</span> homeaddr <span>|</span>
<span>+</span><span>–</span><span>—-+——+——–+——+——+——————–+</span>
<span>|</span> <span>1</span> <span>|</span> <span>1001</span> <span>|</span> 张三 <span>|</span> <span>26</span> <span>|</span> 男 <span>|</span> 北京市海淀区 <span>|</span>
<span>|</span> <span>2</span> <span>|</span> <span>1001</span> <span>|</span> 李四 <span>|</span> <span>24</span> <span>|</span> 女 <span>|</span> 北京市昌平区 <span>|</span>
<span>|</span> <span>3</span> <span>|</span> <span>1002</span> <span>|</span> 王五 <span>|</span> <span>25</span> <span>|</span> 男 <span>|</span> 湖南长沙市 <span>|</span>
<span>|</span> <span>4</span> <span>|</span> <span>1004</span> <span>|</span> Aric <span>|</span> <span>15</span> <span>|</span> 男 <span>|</span> England <span>|</span>
<span>+</span><span>–</span><span>—-+——+——–+——+——+——————–+</span>
<span>4</span> rows <span>in</span> <span>set</span> (<span>0.00</span> sec)

any为任意一个条件即可,d_id不为任意一个结果即可,由于department有一个d_id为1003,所有employee的d_id不为1003即可返回结果,满足条件,所有返回所有结果

mysql > select * from employee

->where d_id = any(

select d_id from department);

结果为


loop每一个结果,d_id满足其中一条结果即可,employee有个d_id为1004的记录,不在department中d_id列表中,所有,d_id为1004的记录结果被过滤,不显示。





##### 带ALL关键字的子查询

ALL关键字表示满足其中所有条件


mysql> SELECT * FROM employee
-> WHERE d_id>=ALL
-> (SELECT d_id FROM department);
+——+——+——+——+——+———-+
| num | d_id | name | age | sex | homeaddr |
+——+——+——+——+——+———-+
| 4 | 1004 | Aric | 15 | 男 | England |
+——+——+——+——+——+———-+
1 row in set (0.00 sec)