ocp-047-29 子查询

29. A non-correlated subquery can be defined as ____. 
A. a set of sequential queries, all of which must always returna single value 
B. a set of sequential queries, all of which must return valuesfrom the same 
table 
C. a SELECT statement that can be embedded in a clause of another SELECT 
statement only 
D. a set of one or more sequential queries in which generally the result of 
the inner query is used as the search value in the outer query 
Answer: D 
Non-correlate:非直线相关 embed:使..嵌入 
Instead, the correlated subquery is executing once for each value that 
the parent query finds for each row 
子查询：嵌套在其他查询中的查询称之。 
子查询又称内部，而包含子查询的语句称之外部查询（又称主查询）。 
所有的子查询可以分为两类，即相关子查询和非相关子查询 
1>非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将
值传递给外部查询。 
2>相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一
次。 
故非相关子查询比相关子查询效率高 
--非相关子查询 
SELECT EMPNO, LASTNAME 
FROM EMPLOYEE 
WHERE WORKDEPT = A00 
AND SALARY > (SELECT AVG(SALARY) 
FROM EMPLOYEE 
WHERE WORKDEPT = A00) –子查询是一个独立的查询 
--相关子查询 
SELECT E1.EMPNO, E1.LASTNAME, E1.WORKDEPT 
FROM EMPLOYEE E1 
WHERE SALARY > (SELECT AVG(SALARY) 
FROM EMPLOYEE E2 
WHERE E2.WORKDEPT = E1.WORKDEPT) –依赖于外部查询结果 
ORDER BY E1.WORKDEPT