課程目標：

多列子查詢
標量子查詢
關聯子查詢
exists運算子
with語句

1

、多列子查詢

語法：

有2種多列比較：

非成對比較
成對比較，沒有交叉

例子：成對比較子查詢，查詢與姓為John的人員在同一部門且屬於同一上司的員工資訊

SQL> select employee_id,manager_id,department_id from employees where first_name='John';

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID

----------- ---------- -------------

110 108 100

145 100 80

139 123 50

SQL> select employee_id,manager_id,department_id from employees

2 where (manager_id,department_id) in(

3 select manager_id,department_id from employees where first_name='John')

4 and first_name<>'John';

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID

----------- ---------- -------------

137 123 50

15 rows selected.

例子：非成對比較子查詢，查詢與first_name為John的人員同一個部門同事以及與first_name為John的人員同一個上司的同事

SQL> select employee_id,manager_id,department_id from employees

2 where manager_id in(select manager_id from employees where first_name='John')

3 and department_id in(select department_id from employees where first_name='John')

4 and first_name<>'John';

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID

----------- ---------- -------------

120 100 50

20 rows selected.

2、標量子查詢表示式（scalar subquery）

只返回一個記錄的子查詢。

scalar subquery用於如下情況：

decode和case的條件和表示式部分
沒有group by的select的語句
update語句的set和where部分

例子：標量子查詢，當部門編號等於位置編號為1800的部門編號，則顯示位置為Canada，否則顯示USA

SQL> select employee_id,last_name,

2 (case when department_id=(select department_id from departments where location_id=1800)

3 then 'Canada' else 'USA' end) location

4 from employees;

EMPLOYEE_ID LAST_NAME LOCATI

----------- ------------------------- ------

198 OConnell USA

3、關聯子查詢

在關聯子查詢中，對於外部查詢返回的每一行資料，內部查詢都要執行一次。另外，在關聯子查詢中資訊流是雙向的。外部查詢的每行資料傳遞一個值給子查詢，然後子查詢為每一行資料執行一次並返回它的記錄。然後，外部查詢根據返回的記錄做出決策。

語法：

處理過程如下：

1）外查詢取出一行

2）從取出行中儲存子查詢需要的欄位

3）根據儲存的欄位執行子查詢

4）在外查詢where語句中對子查詢執行結果進行確認

5）重複執行以上過程

例子：查詢比部門平均薪水高的人員資訊

SQL> select last_name,salary,department_id from employees;

SQL> select department_id,avg(salary) from employees group by department_id;

SQL> select last_name,salary,department_id from employees outer

2 where salary>(select avg(salary) from employees where department_id=outer.department_id);

LAST_NAME SALARY DEPARTMENT_ID

------------------------- ---------- -------------

Fay 6000 10

例子：查詢職位變動過至少2次的人

SQL> select e.employee_id,last_name,e.job_id from employees e

2 where 2<=(select count(*) from job_history where employee_id=e.employee_id);

EMPLOYEE_ID LAST_NAME JOB_ID

----------- ------------------------- ----------

200 Whalen AD_ASST

4、exists運算子

比較子查詢的結果是否有記錄的存在，如果有記錄，則子查詢返回true且結束搜尋，如果沒有記錄，則子查詢返回false且繼續搜尋。

例子：查詢至少有一個下屬的領導資訊

SQL> select employee_id,last_name,job_id,department_id from employees outer

2 where exists(select 'X' from employees where manager_id=outer.employee_id);

EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID

----------- ------------------------- ---------- -------------

201 Hartstein MK_MAN 20

其中'X'是一個佔位符，沒有任何意義，使用數字也可以哦。

也可以使用in

SQL> select employee_id,last_name,job_id from employees outer

2 where employee_id in(select manager_id from employees where manager_id=outer.employee_id);

EMPLOYEE_ID LAST_NAME JOB_ID

----------- ------------------------- ----------

201 Hartstein MK_MAN

例子：查詢沒有人員的部門

SQL> select department_id,department_name from departments d

2 where not exists(select 1 from employees where department_id=d.department_id);

DEPARTMENT_ID DEPARTMENT_NAME

------------- ------------------------------

120 Treasury

SQL> select department_id,department_name from departments outer

2 where department_id not in (select department_id from employees where department_id is not null);

DEPARTMENT_ID DEPARTMENT_NAME

------------- ------------------------------

120 Treasury

SQL> select department_id,department_name from departments outer

2 where department_id not in(select department_id from employees where department_id=outer.department_id);

DEPARTMENT_ID DEPARTMENT_NAME

------------- ------------------------------

120 Treasury

5、關聯更新

使用關聯子查詢用一張表的資料對另一張表進行更新

語法：

例子：使用部門表的部門名字更新人員表的部門名字

SQL> create table empl6 as select * from employees;

Table created.

SQL> alter table empl6 add(department_name varchar2(25));

Table altered.

SQL> update empl6 e

2 set department_name=(select department_name from departments where department_id=e.department_id);

108 rows updated.

更新empl6表，將人員編號大於180的人員的部門名稱修改為abc，部門編號修改為500

SQL> update empl6 set department_name='abc',department_id=500 where employee_id>180;

27 rows updated.

SQL> commit;

Commit complete.

再次進行關聯更新

SQL> update empl6 e

2 set department_name=(select department_name from departments where department_id=e.department_id);

108 rows updated.

SQL> select department_name,department_id from empl6 where employee_id>180;

DEPARTMENT_NAME DEPARTMENT_ID

------------------------- -------------

500

可以看到這兒部門名稱被更新為null了，這兒要注意，如果子查詢裡面的表沒有對應的記錄，那麼更新的記錄會被置為null，而不是保持不變。那麼如果要使這些記錄保持不變，可以使用where語句加上條件只更新在departments表裡面有的部門名稱。

先回滾

SQL> rollback;

Rollback complete.

再來進行關聯更新

SQL> update empl6 e

2 set department_name=(select department_name from departments where department_id=e.department_id)

3 where exists(select department_name from departments where department_id=e.department_id);

80 rows updated.

檢視確認

SQL> select department_name,department_id from empl6 where employee_id>180;

DEPARTMENT_NAME DEPARTMENT_ID

------------------------- -------------

abc 500

6、關聯刪除

使用關聯子查詢用一張表的資料對另一張表進行刪除

語法：

例子：刪除表empl6裡面更換過工作的人員

SQL> delete from empl6 e

2 where employee_id in (select employee_id from job_history where employee_id=e.employee_id);

8 rows deleted.

SQL> select distinct employee_id from job_history;

EMPLOYEE_ID

-----------

101

8 rows selected.

7、with語句

儲存在使用者的臨時表空間
可以提高效能
只能用於select statements
可以包含多個查詢，用逗號分隔
先定義，後使用，對其後面的的查詢塊及主查詢可見

例子：使用with語句，查詢每個部門的名稱及其薪水總計，同時這些部門的薪水總計大於公司所有部門的平均薪水

使用with語句定義每個部門的薪水總計及所有部門的平均薪水

SQL> with

2 dept_costs as(

3 select d.department_name,sum(e.salary) as dept_total

4 from employees e join departments d on e.department_id=d.department_id

5 group by d.department_name),

6 avg_cost as(

7 select sum(dept_total)/count(*) as dept_avg from dept_costs)

8 select * from dept_costs where dept_total>(select dept_avg from avg_cost) order by department_name;

DEPARTMENT_NAME DEPT_TOTAL

------------------------------ ----------

Sales 304500

Shipping 206400

檢視他的執行計劃，建立了一個臨時表，提高了效能

SQL> explain plan for

2 with

3 dept_costs as(

4 select d.department_name,sum(e.salary) as dept_total

5 from employees e join departments d on e.department_id=d.department_id

6 group by d.department_name),

7 avg_cost as(

8 select sum(dept_total)/count(*) as dept_avg from dept_costs)

9 select * from dept_costs where dept_total>(select dept_avg from avg_cost) order by department_name;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3967601111

-------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 27 | 810 | 12 (25)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | | | | | |

| 3 | HASH GROUP BY | | 27 | 621 | 7 (29)| 00:00:01 |

| 4 | MERGE JOIN | | 107 | 2461 | 6 (17)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

| 6 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |

|* 7 | SORT JOIN | | 108 | 756 | 4 (25)| 00:00:01 |

| 8 | TABLE ACCESS FULL | EMPLOYEES | 108 | 756 | 3 (0)| 00:00:01 |

| 9 | SORT ORDER BY | | 27 | 810 | 5 (20)| 00:00:01 |

|* 10 | VIEW | | 27 | 810 | 2 (0)| 00:00:01 |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_25A4FE | 27 | 621 | 2 (0)| 00:00:01 |

| 12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |

| 13 | SORT AGGREGATE | | 1 | 13 | | |

| 14 | VIEW | | 27 | 351 | 2 (0)| 00:00:01 |

| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_25A4FE | 27 | 621 | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

10 - filter("DEPT_TOTAL"> (SELECT "DEPT_AVG" FROM (SELECT SUM("DEPT_TOTAL")/COUNT(*) "DEPT_AVG"

FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "DEPARTMENT_NAME","C1" "DEPT_TOTAL" FROM

"SYS"."SYS_TEMP_0FD9D6610_25A4FE" "T1") "DEPT_COSTS") "AVG_COST"))

8、相關習題：

（1）Which two statements are true regarding the execution of the correlated subqueries ?(Choose two)

A.The nested query executes after the outer query returns the row.

B.The nested query executes first and then the outer query executes.

C.The outer query executes only once for the result returned by the inner query.

D.Each row returned by the outer query is evaluated for the results returned by the inner query.

答案：AD

（2）Which two statements are true regarding the EXISTS operator used in the correlated subqueries?(choose two)

A.The outer query stops evaluating the result set of the inner query when the first value is found.

B.It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

C.It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

D.The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

答案：AC

（3）View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables. You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get the maximum salary in their respective departments. The following SQL statement was written: WITH SELECT employee_id, last_name, salary FROM employees WHERE (department_id, salary) = ANY (SELECT * FROM dept_max) dept_max as ( SELECT d.department_id, max(salary) FROM departments d JOIN employees j ON (d.department_id = j.department_id) GROUP BY d.department_id)? Which statement is true regarding the execution and the output of this statement?

A.The statement would execute and give the desired results.

B.The statement would not execute because the = ANY comparison operator is used instead of =.

C.The statement would not execute because the main query block uses the query name before it is even created.

D.The statement would not execute because the comma is missing between the main query block and the query name

答案：C

（4）View the Exhibit and examine DEPARTMENTS and the LOCATION tables. Evaluate the following SQL statement: SELECT location_id, city FROM locations l WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> l.location_id)? This statement was written to display LOCATION_ID and CITY where there are no departmnts located. Which statement is true regarding the execution and output of the command?

A.The statement would execute and would return the desired results.

B.The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.

C.The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>.

D.The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.

答案：C

（5）The following are the steps for a correlated subquery, listed in random order: 1) The WHERE clause of the outer query is evaluated. 2) The candidate row is fetched from the table specified in the outer query. 3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed. 4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.

A.4, 2, 1, 3

B.4, 1, 2, 3

C.2, 4, 1, 3

D.2, 1, 4, 3

答案：C

（6）Which two statements best describe the benefits of using the WITH clause？(Choose two.)
A.It enables users to store the results of a query permanently.
B.It enables users to store the query block permanently in the memory and use it to create complex queries.
C.It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
D.It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace.

答案：CD

（7）Which statements are true regarding the usage of the WITH clause in complex correlated subqueries？(Choose all that apply.)
A.It can be used only with the SELECT clause.
B.The WITH clause can hold more than one query.
C.If the query block name and the table name were the same, then the table name would take precedence.
D.The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block.

答案：ABD

（8）View the Exhibit and examine the structure of EMPLOYEES and JOB_HISTORY tables. The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table. Which two SQL statements can you execute to accomplish the task?(Choose two.)

A.DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id) AND job_id = (SELECT job_id FROM employees e WHERE j.job_id = e.job_id)；
B.DELETE FROM job_history j WHERE (employee_id, job_id) = ALL (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )；
C.DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )；
D.DELETE FROM job_history j WHERE (employee_id, job_id) = (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )；

答案：CD

OCP課程19：SQL之使用子查詢檢索資料

相關文章