OCP-1Z0-051 第138题 not操作,IN、ANY、ALL的应用-CSDN博客

本文链接：https://blog.csdn.net/holly2008/article/details/25978187

本文探讨了Oracle数据库中子查询的使用，特别是NOT操作符与IN、ANY、ALL操作符在多行子查询中的结合应用。通过示例展示了NOT IN、NOT ANY及NOT ALL的查询效果，验证了D选项的正确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、原题

Which statement is true regarding subqueries?
A. The LIKE operator cannot be used with single- row subqueries.
B. The NOT IN operator is equivalent to IS NULL with single- row subqueries.
C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.
D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.

答案：D

二、题目翻译

关于子查询哪句话是正确的？
A.LIKE操作符不能用在单行子查询中。
B.NOT IN操作符相当于单选子查询的IS NULL。
C.多行子查询中的=ANY and =ALL操作符具有相同的功能。
D.NOT操作可以用在多行子查询中的IN,ANY和ALL操作符上。

三、测试
D选项，进行如下测试：

SQL> select ename,sal
2 from emp
3 where not sal IN (select sal from emp where ename='SCOTT' or ename='FORD');

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

12 rows selected.

SQL> select ename,sal
2 from emp
3 where sal not IN (select sal from emp where ename='SCOTT' or ename='FORD');

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

12 rows selected.

SQL> select ename,sal
2 from emp
3 where not sal=ANY(select sal from emp where ename='SCOTT' or ename='FORD');

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

12 rows selected.

SQL> select ename,sal
2 from emp
3 where not sal=ALL(select sal from emp where ename='SCOTT' or ename='FORD');

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
KING                       5000
TURNER                     1500
ADAMS                      1100
JAMES                       950
MILLER                     1300

12 rows selected.