ocp-047-66

66. Which two statements are true regarding multiple-row subque ries? (Choose 
two.) 
A. They can contain group functions. 
B. They always contain a subquery within a subquery. 
C. They use the < ALL operator to imply less than the maximum. 
D. They can be used to retrieve multiple rows from a single table only. 
E. They should not be used with the NOT IN operator in the main query if NULL 
is likely to be a part of the result of the subquery. 
Answer: AE 
SQL> select dest,revenue from test where revenue in ( 
    select 4000 from test union  select null from dual); 
DEST          REVENUE 
---------- ---------- 
wenzhou          4000 
SQL> select dest,revenue 
  from test where revenue not in ( 
select 4000 from test union  select null from dual); 
未选定行 
SQL> select dest,revenue 
    from test where revenue not in (select null from dual); 
未选定行 

可以结论:只要子查询里有空值,在主查询就不能用not in,因为不能返回任何值。

ingle-row and multiple-row subqueries
Subqueries that can return only one or zero rows to the outer statement are called single-row subqueries. Single-row subqueries are subqueries used with a comparison operator in a WHERE, or HAVING clause.
Subqueries that can return more than one row (but only one column) to the outer statement are called multiple-row subqueries. Multiple-row subqueries are subqueries used with an IN, ANY, or ALL clause.