3-2-5:進階查詢 (select 合併查詢、子查詢)

合併 (Join)

是指將兩關聯表R1與R2依合併條件合併成一個新的關聯表R3,透過SELECT指令中WHERE部分的等式,即對等合併(Equi-Join),例如:

SELECT 欄位串列
FROM 資料表A, 資料表B
WHERE A.欄位 = B.欄位 and 其他條件  

子查詢 (Subquery)

在一個 SQL 語法的條件子句中,放入另一個 SQL 語法稱為子查詢 (Subquery)。 例如:

SELECT … FROM 資料表
WHERE <欄位名或欄位運算式> <比較運算子>
  ( SELECT … FROM資料表
    WHERE 條件 );

範例

某公司的員工資料表Emp及部門資料表Depart如下:

員工Emp

eidenamesexaddress(地址)salary(薪水)dept(部門)
e001王大同中壢區健行路20000d01
e002李小明八德區介壽路25000d01
e003林上華平鎮區中豐路30000d02
e004丁中民中壢區中正路32000d03
e005陳文心平鎮區民族路22000d01
e006李小英中壢區中山路28000d03

部門Depart

deptiddnamemanager(經理)
d01 企劃部e005
d02設計部e003
d03行銷部e004

題目1

請計算在企劃部員工的平均薪資。

【解】觀看教學影片(JOIN) 觀看教學影片(子查詢)

# 方法一
select avg(Salary) from Emp E, Depart D
where E.dept = D.deptid and D.dname = '企劃部'
# 方法二
select avg(Salary) from Emp E
where E.dept = 
 (select D.deptid from Depart D
  where D.dname = '企劃部')

題目2

請計算各部門員工的平均薪資。

【解】觀看教學影片

select D.deptid, avg(salary)
from Emp E, Depart D
where E.dept = D.deptid
group by D.deptid

題目3

請計算企劃部與設計部員工的平均薪資各是多少。

【解】觀看教學影片

# 方法一
select D.deptid, avg(Salary)
from Emp E, Depart D
where E.dept=D.deptid and D.dname in ('企劃部', '設計部')
group by D.deptid
# 方法二
select D.deptid, avg(Salary)
from Emp E, Depart D
where E.dept = D.deptid
group by D.deptid
having D.dname in ('企劃部', '設計部')

題目4

請算出薪資高於平均薪資的有幾名員工。

【解】觀看教學影片

select count(ename) from Emp
where salary > (select avg(salary) from Emp)

題目5

請列出薪資高於平均薪資的員工姓名。

【解】觀看教學影片

select ename from Emp
where salary > (select avg(salary) from Emp)