SQL學習之子查詢，基於Oracle下的HR使用者（四）

六、 子查詢
1 子查詢介紹
1.1 什麼是子查詢
子查詢是一個 SELECT 語句，它是嵌在另一個 SELECT 語句中的子句。
可以用組合兩個查詢的方法解決這個問題，放置一個查詢到另一個查詢中。內查詢或子查詢返回一個值給外查詢或主查詢。使用一個子查詢相當於執行兩個連續查詢並且用第一個查詢的結果作為第二個查詢的搜尋值。
1.2 子查詢語法

• 子查詢 (內查詢) 在主查詢之前執行一次
• 子查詢的結果被用於主查詢 (外查詢)
可以將子查詢放在許多的 SQL 子句中，包括：
• WHERE 子句
• HAVING 子句
• FROM 子句
2 使用子查詢
2.1 使用子查詢的原則
• 子查詢放在圓括號中。
• 將子查詢放在比較條件的右邊。
• 在單行子查詢中用單行運算子，在多行子查詢中用多行運算子。
2.1.1 示例
誰的薪水比Abel高。用內連線實現：

select em.last_name,em.salary from employees abel,employees em 
where abel.last_name = 'Abel' and em.salary > abel.salary;

用子查詢實現：

select em.last_name,em.salary from employees em where em.salary >(
select m.salary from employees m where m.last_name ='Abel');

3 子查詢的型別
• 單行子查詢：子查詢語句只返回一行的查詢
• 多行子查詢：子查詢語句返回多行的查詢

3.1 單行子查詢
• 僅返回一行
• 使用單行比較符
主查詢對子查詢結果的單行比較運算子：
3.1.1 示例一
顯示那些 job ID 與僱員 141 相同的僱員的名字與 job ID。

select em.last_name,em.job_id from employees em where em.job_id = (
select job_id from employees e where e.employee_id = 141);

3.1.2 示例二
顯示 job ID 與僱員 141 相同，並且薪水 高於僱員 143 的那些僱員。

select e.last_name,e.job_id,e.salary from employees e where e.job_id = (
select em.job_id from employees em where em.employee_id = 141) and e.salary > (
select emp.salary from employees emp where emp.employee_id = 143);

3.2 在子查詢中使用組函式
在子查詢中也可使用組函式。
3.2.1 示例
顯示所有其薪水等於最低薪水的僱員的 last name、job ID 和 salary。

select em.last_name,em.job_id,em.salary from employees em 
where em.salary =(select min(salary) from employees);

3.3 帶子查詢的 HAVING 子句
可以在 WHERE 子句中使用子查詢，也可以在 HAVING 子句中使用子查詢。
3.3.1 示例
顯示所有其最低薪水大於 部門 50 的最低薪水的部門號和最低薪水。

select em.department_id,min(em.salary) from employees em group by em.department_id having min(em.salary) > (
select min(e.salary) from employees e where e.department_id = 50);

3.4 什麼是子查詢錯誤?

子查詢錯誤：使用子查詢的一個常見的錯誤是單行子查詢返回了多行。
3.5 多行子查詢
• 返回多於一行
• 使用多行比較符
主查詢對子查詢的多行比較運算子

操作	含義
in	等於列表中的任何成員
any	比較子查詢返回的每一個值
all	比較子查詢返回的全部值

在條件中也可使用 NOT 取反。
3.6 在多行子查詢中使用 IN 運算子
3.6.1 示例
查詢各部門收入為部門最低的那些僱員。顯示他們的名字，薪水以及部門 ID。

select e.last_name,e.department_id,e.salary  from employees e where e.salary in(
select min(em.salary) from employees em group by em.department_id);

3.7 在多行子查詢中使用 ANY 運算子

< ANY 意思是小於最大值。 >ANY 意思是大於最小值。

3.7.1 示例
顯示工作崗位不是 IT_PROG 的僱員，並且這些僱員的的薪水少於 IT_PROG 工作崗位的僱員的 ID、名字、工作崗位和薪水。

select e.employee_id,e.last_name,e.job_id,e.salary from employees e where e.job_id <> 'IT_PROG' and e.salary < any (
select em.salary from employees em where em.job_id = 'IT_PROG') ;

3.8 在多行子查詢中使用 ALL 運算子

<ALL 意思是小於最小值。>ALL 意思是大於最大值。

ANY 與 ALL 的區別：

ANY: >ANY 表示至少大於一個值，即大於最小值。
ALL: >ALL 表示大於每一個值，既大於最大值。

3.8.1 示例
顯示那些薪水低於工作崗位 IT_PROG 的最低薪水，並且工作崗位不是 IT_PROG 的所有僱員。
4 子查詢小結練習
4.1 寫一個查詢顯示與 Zlotkey 在同一部門的僱員的 last name
和 hire date，結果中不包括 Zlotkey。

select em.last_name,em.hire_date from employees em where em.department_id = (
select e.department_id from employees e where e.last_name = 'Zlotkey');

4.2 建立一個查詢顯示所有其薪水高於平均薪水的僱員的僱員號和名字。按薪水的升序排序。

select em.employee_id,em.last_name from employees em where em.salary > (
select avg(salary) from employees) order by em.salary;

4.3 寫一個查詢顯示所有工作在有任一僱員的名字中包含一個 u的部門的僱員的僱員號和名字。

select e.employee_id,e.last_name from employees e where e.department_id in(
select em.department_id from employees em where em.last_name like '%u%');

4.4 顯示所有部門地點號 (department location ID ) 是 1700的僱員的 last name、department number 和 job ID。

select em.last_name,em.department_id,em.job_id from employees em where em.department_id in(
select d.department_id from departments d where d.location_id = 1700);

4.5 顯示每個向 King 報告的僱員的名字和薪水。

select e.last_name,e.salary from employees e where e.manager_id in(
select em.employee_id from employees em where em.last_name = 'King');

4.6 顯 示 在 Executive 部 門 的 每 個 僱 員 的 department number、last name 和 job ID。

select em.department_id,em.last_name,em.job_id from employees em where em.department_id = (
select d.department_id from departments d where d.department_name ='Executive');

4.7 查詢顯示所有收入高於平均薪水並且工作在有任一僱員的名字中帶有一個 u 的部門的僱員的 employee numbers、last、names 和 salaries。

select emp.employee_id,emp.last_name,emp.salary from employees emp where emp.salary > (
select avg(salary) from employees) and emp.department_id in(
select em.department_id from employees em where em.last_name like '%u%');