13、(6-7) choose two
Which two statements are true regarding operators used with subqueries? (Choose two.)
A) =ANY and =ALL operators have the same functionality.
E) The NOT IN operator is equivalent to is NULL.
C) The <ANY operator means less than the maximum.
D) The NOT operator can be used with IN, ANY and ALL operators.
E) The IN operator cannot be used in single-row subqueries.
Answer:CD
(解析:051 曾经出现过,但是当时是单选题,答案是 D,这个语法比较少见。
其实就是把 NOT 放到整个表达式之前,下面条件执行的结果都是一样,考大家的逻辑思维能力:
WHERE NOT col IN (SELECT ...)(也可以是 WHERE col not IN (SELECT ...))
WHERE NOT col = ANY (SELECT ...)(大于、小于都可以)
WHERE NOT col = ALL (SELECT ...)(大于、小于都可以)
SQL> select deptno from emp
2 where not deptno in (select deptno from dept where deptno=10);
DEPTNO

20
30
30
....
或者:
select deptno from emp
where not deptno = all (select deptno from dept where deptno=10);