《SQL CookBook 》筆記-第三章-多表查詢

摘要： 第三章 shanzm 注：筆記中的SQL語句只在SQL Server2014上測試過，不一定適應其他的DBMS，SQL server預設輸出結果是網格格式，在此之後改為文字格式。 3.1 疊加兩個行集 問題： 你想返回儲存在多個表中的資料 解決方案： ...

第三章

shanzm

注：筆記中的SQL語句只在SQL Server2014上測試過，不一定適應其他的DBMS，SQL server預設輸出結果是網格格式，在此之後改為文字格式。

3.1 疊加兩個行集

問題：

你想返回儲存在多個表中的資料

解決方案：

聯合查詢，使用union all 合併多個表中的行

select ename as ename_and_dname,deptno
from EMP
where deptno=10

union all 

select '----------',null
from T1

union all

select dname,deptno
from DEPT

結果：

ename_and_dnamedeptno
--------------------------
CLARK10
KING10
MILLER10
----------NULL
ACCOUNTING10
RESEARCH20
SALES30
OPERATIONS40

(8 行受影響)

【注意】

1. 多個select查詢使用union all 連線，查詢的列的結果放在一個結果集中，但是有一點要注意的是，每一個select的的列數必須相同，且每一個select查詢的每一列的資料型別必須匹配 。

2. 如果每一個select查詢的結果會有重複的行，那麼可以使用union 來連線每一個查詢。

   具體看下例：

   使用union all

select deptno
from EMP
union all
select deptno
from DEPT

結果：

deptno
-----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
10
20
30
40

(18 行受影響)

使用union

select deptno
from emp
union
select deptno
from dept

結果：

deptno
-----------
10
20
30
40

(4 行受影響)

大體而言，使用union 就相當於針對union all 的輸出結果在執行一次distinct 操作

如下：

select distinct deptno
from
(
select deptno
from emp
union all
select deptno
from dept 
)

關於優化，除非有必要，否則不要在查詢中使用 DISTINCT 操作，同樣的規則也適用於 UNION 。除非有
必要，否則不要用 UNION 代替 UNION ALL

3.2 合併相關行

問題：

如果資料儲存在多個表中，怎樣用一條 SELECT 語句就檢索出資料呢？

例如：

你想顯示部門編號為 10 的全部員工的名字及其部門所在地，但這些資料分別儲存在兩個表裡

解決方案：

使用連線。

聯結是一種機制，用來在一條 SELECT 語句中關聯表，因此稱為聯結。使用特殊的語法，可以聯結多個表返回一組輸出，聯結在執行時關聯表中正確的行。

select e.ename,d.loc
from EMP as e,DEPT as d
where e.deptno=d.deptno and e.deptno=10

結果：

ename	loc
CLARK	NEW YORK
KING	NEW YORK
MILLER	NEW YORK

【說明】

1. 首先關於連線 ，必須要理解什麼是關係資料庫 。

關係表的設計就是要把資訊分解成多個表，一類資料一個表。各表通過某些共同的值互相關聯（所以才叫關係資料庫）。

連線查詢就是查詢多個表中的資料然後一起返回。

1. 使用表的連線查詢，為了防止產生歧義，就要使用完全限定名，即：表名.列名。

需要注意，表別名只在查詢執行中使用。與列別名不一樣，表別名不返回到客戶端。

此處我們使用的是表別名來簡寫，在from子句中我們在帶查詢的表後使用as 寫別名，其中as可以省略。

因為Oracle不需要使用as，所以我們以後也就預設省略as。

1. 如果沒有 WHERE子句，第一個表中的每一行將與第二個表中的每一行配對，而不管它們邏輯上是否能配在一起（這就是笛卡爾積）。

由沒有聯結條件的表關係返回的結果為笛卡兒積 。檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數。

【說明】

內連線又稱為等值連線(equal join),他是基於兩個表之間的某列相等來做連線。

上面的例子就是內連線，但是那稱為隱式的內連線 ，

我們也可以使用顯示的內連線

顯示的內連線使用inner join 連線兩個表，使用on 子句做連線條件。

select e.ename,d.loc
from EMP as e inner join DEPT as d
on e.deptno=d.deptno and e.deptno=10

【注意】關於SQL優化：不要聯結不必要表。聯結的表越多，效能下降越厲害。

3.3 查詢兩個表中相同的行

問題:

你想找出兩個表中相同的行

create view V
as
select ename,job,sal
from emp
where job = 'CLERK'

select * from V

ENAMEJOBSAL
---------- --------- ----------
SMITHCLERK800
ADAMSCLERK1100
JAMESCLERK950
MILLERCLERK1300

檢視 V 只包含職位是 CLERK 的員工，但並沒有顯示 EMP 表中所有可能的列。你想從 EMP 表獲取與檢視 V 相匹配的全部員工的 EMPNO 、 ENAME 、 JOB 、 SAL 和 DEPTNO ，並且希望得到如下所示的結果集:

EMPNOENAMEJOBSALDEPTNO
-------- ---------- --------- ---------- ----------
7369SMITHCLERK80020
7876ADAMSCLERK110020
7900JAMESCLERK95030
7934MILLERCLERK130010

解決方案:使用連線查詢

select e.empno,e.ename,e.job,e.sal,e.deptno
from emp e, V
where e.ename = v.ename
and e.job = v.job
and e.sal = v.sal

也可以使用join 寫法

select e.empno,e.ename,e.job,e.sal,e.deptno
from emp e join V
on 
( e.ename = v.ename
and e.job = v.job
and e.sal = v.sal 
)

3.4 查詢只存在於一個表中的資料

問題:

你希望從一個表（可以稱之為源表）裡找出那些在某個目標表裡不存在的值。

例如：

你想找出在 DEPT 表中存在而在 EMP 表裡卻不存在的部門編號（如果有的話）。

解決方案：

使用子查詢得到 EMP 表中所有的 DEPTNO ，並將該結果傳入外層查詢，然後外層查詢會檢索DEPT 表，找出沒有出現在子查詢結果裡的 DEPTNO 值。

select deptno from DEPT where deptno not in (select deptno from EMP)

結果：

deptno
40

【注意】如果子查詢select deptno from EMP 的返回值的中有NUll值，就會複雜了。

因為IN 和NOT IN 本質上是 OR 運算，由於 Null 值參與 OR 邏輯運算的方式不同， IN 和 NOT IN 將會產生不同的結果。

考慮以下分別使用 IN 和 OR 的例子

其實我們知道DEPT表中的deptno列中的值有10，沒有50和null

select deptno
from dept
where deptno in ( 10,50,null )

結果：

DEPTNO
10

select deptno
from dept
where (deptno=10 or deptno=50 or deptno=null)

結果：

DEPTNO
10

再來看看使用 NOT IN 和 NOT OR 的例子:

select deptno
from dept
where deptno not in ( 10,50,null )

結果：

無

select deptno
from dept
where not (deptno=10 or deptno=50 or deptno=null)

結果：

無

其中：

in ( 10,50,null )等價於(deptno=10 or deptno=50 or deptno=null)

【分析】

and的真值表

	true	flase	null
true	true	flase	null
flase	flase	flase	flase
null	null	flase	null

or的真值表

	true	flase	null
true	true	true	true
flase	true	flase	null
null	true	null	null

not的真值表

true	flase	null
flase	true	null

切記：在 SQL 中， TRUE or NULL 的運算結果是 TRUE ，但 FALSE or NULL 的運算結果卻是 Null

必須謹記，當使用 IN 謂詞以及當執行 OR 邏輯運算的時候，你要想到是否會涉及 Null 值

【注意】

為了避免 NOT IN 和 Null 值帶來的問題，需要結合使用 NOT EXISTS 和關聯子查詢。關聯子查詢指的是外層查詢執行後獲得的結果集會被內層子查詢引用。下面的例子給出了一個免受 Null 值影響的替代方案

回到“問題”--你想找出在 DEPT 表中存在而在 EMP 表裡卻不存在的部門編號（如果有的話）

select d.demptno
from DEPT d
where not exists
(
select null
from EMP e
where e.deptno=d.deptno
)

【分析】

1. 如果子查詢有結果返回給外層查詢，那麼 EXISTS (...) 的評估結果是 TRUE ，這樣 NOT EXISTS (...) 就是 FALSE ，如此一來，外層查詢就會捨棄當前行。
2. 如果子查詢沒有返回任何結果，那麼 NOT EXISTS (...) 的評估結果是 TRUE ，由此外層查詢就會返回當前行（因為它是一個不存在於 EMP 表中的部門編號）。
3. 把 EXISTS/NOT EXISTS 和關聯子查詢一起使用時， SELECT 列表裡的專案並不重要，因此我在這個例子中用了 SELECT NULL

3.5 從一個表檢索與另一個表不相關的行

問題：

兩個表有相同的鍵，你想在一個表裡查詢與另一個表不相匹配的行。

例如：

你想找出哪些部門沒有員工。

分析：

你發現好像和3.3 查詢只存在於一個表中的資料 是一樣的，只要修改為

select d.*
from dept d
where not exists 
( 
select null
from emp e
where d.deptno = e.deptno
)

當然我們還有其他方法：使用外連線

select d.*
from dept d left outer join emp e
on (d.deptno = e.deptno)
where e.deptno is null

結果如下：

DEPTNODNAMELOC
----------- -------------- -------------
40OPERATIONSBOSTON

(1 行受影響)

3.6 新增連線查詢而不影響其他連線查詢

問題:

你已經有了一個查詢語句，它可以返回你想要的資料。你需要一些額外資訊，但當你試圖獲取這些資訊的時候，卻丟失了原有的查詢結果集中的資料。

例如:

首先，這個例子我們要補充一個員工獎金錶EMP_Bonus

EMPNORECEIVEDTYPE
-------------------------------
736914-MAR-20051
790014-MAR-20052
778814-MAR-20053

你想查詢所有員工的資訊，包括他們所在部門的位置，以及他們收到獎金的日期。

最初，你使用如下所示的查詢語句

select e.ename, d.loc
from emp e, dept d
where e.deptno=d.deptno

結果：

ENAMELOC
-----------------------
SMITHDALLAS
ALLENCHICAGO
WARDCHICAGO
JONESDALLAS
MARTINCHICAGO
BLAKECHICAGO
CLARKNEW YORK
SCOTTDALLAS
KINGNEW YORK
TURNERCHICAGO
ADAMSDALLAS
JAMESCHICAGO
FORDDALLAS
MILLERNEW YORK

對於有獎金的員工，你希望把他們收到獎金的日期也新增到結果集裡，但連線了 EMP_BONUS 表後得到的行數卻比預期的要少，因為並非所有的員工都有獎金。

如下：

select e.ename, d.loc,eb.received
from emp e, dept d, emp_bonus eb
where e.deptno=d.deptno
and e.empno=eb.empno

ENAMELOCRECEIVED
---------- ------------- -----------
SCOTTDALLAS14-MAR-2005
SMITHDALLAS14-MAR-2005
JAMESCHICAGO14-MAR-2005

但是我們想要的結果是這樣的：

enamelocreceived
---------- ------------- -----------------------
SMITHDALLAS2005-03-14 00:00:00.000
ALLENCHICAGONULL
WARDCHICAGONULL
JONESDALLASNULL
MARTINCHICAGONULL
BLAKECHICAGONULL
CLARKNEW YORKNULL
SCOTTDALLAS2005-03-14 00:00:00.000
KINGNEW YORKNULL
TURNERCHICAGONULL
ADAMSDALLASNULL
JAMESCHICAGO2005-03-14 00:00:00.000
FORDDALLASNULL
MILLERNEW YORKNULL

(14 行受影響)

解決方案1:

使用外連線既能夠獲得額外資訊，又不會丟失原有的資訊。

首先連線 EMP 表和 DEPT 表，得到全部員工和他們所在部門的位置。

然後外連線 EMP_BONUS 表，如果某個員工有獎金，則檢索其收到獎金的日期。

select e.ename, d.loc, eb.received
from emp e join dept d
on (e.deptno=d.deptno)
left join emp_bonus eb
on (e.empno=eb.empno)

解決方案2：

使用標量子查詢 （即把子查詢放置在 SELECT 列表裡）來模仿外連線操作

select e.ename, d.loc,
(
select eb.received from emp_bonus eb 
where eb.empno=e.empno
) as received
from emp e, dept d
where e.deptno=d.deptno

結果同上。

【分析】

1. 外連線查詢會返回一個表中的所有行，以及另一個表中與之匹配的行。外連線之所以能夠解決本問題，是因為它不會過濾掉任何應該被返回的行。

2. 在不破壞當前結果集的情況下，標量子查詢是為現有查詢語句新增額外資料的好辦法。當使用標量子查詢時，必須確保它們返回的是標量值（單值）。如果 SELECT 列表裡的子查詢返回多行，那麼查詢將會出錯

3.7 確定兩個表是否有相同的資料

問題：

你想知道兩個表或兩個視圖裡是否有相同的資料（行數和值）

為了距離我們新建一個檢視V：

create view
as
select * from EMP
where deptno!=10
union all
select * from EMP 
where ename='WARD'

檢視的具體資料如下：

select * from V

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------- ---------- --------- ----------- ----------------------- ----------- ----------- -----------
7369SMITHCLERK79021980-12-17 00:00:00.000 800NULL20
7499ALLENSALESMAN76981981-02-20 00:00:00.000 160030030
7521WARDSALESMAN76981981-02-22 00:00:00.000 125050030
7566JONESMANAGER78391981-04-02 00:00:00.000 2975NULL20
7654MARTINSALESMAN76981981-09-28 00:00:00.000 1250140030
7698BLAKEMANAGER78391981-05-01 00:00:00.000 2850NULL30
7788SCOTTANALYST75661982-12-09 00:00:00.000 3000NULL20
7844TURNERSALESMAN76981981-09-08 00:00:00.000 1500030
7876ADAMSCLERK77881983-01-12 00:00:00.000 1100NULL20
7900JAMESCLERK76981981-12-03 00:00:00.000 950NULL30
7902FORDANALYST75661981-12-03 00:00:00.000 3000NULL20
7521WARDSALESMAN76981981-02-22 00:00:00.000 125050030

(12 行受影響)

這個檢視V的內容和MEP表的不同就是檢視V不包含部門編號為10的員工，同時這個檢視有同時包含兩個ename=WARD的資料。

我們的問題就是要找到檢視V和EMP表的不同之處：

我們渴望的結果是：

empnoenamejobmgrhiredatesalcommdeptnocnt
----------- ---------- --------- ----------- ----------------------- ----------- ----------- ----------- -----------
7521WARDSALESMAN76981981-02-22 00:00:00.000 1250500301
7782CLARKMANAGER78391981-06-09 00:00:00.000 2450NULL101
7839KINGPRESIDENT NULL1981-11-17 00:00:00.000 5000NULL101
7934MILLERCLERK77821982-01-23 00:00:00.000 1300NULL101
7521WARDSALESMAN76981981-02-22 00:00:00.000 1250500302

(5 行受影響)

解決方案：

使用關聯子查詢和 UNION ALL 找出那些存在於檢視 V 而不存在於 EMP 表的資料，以及存在於 EMP 表而不存在於檢視 V 的資料，並將它們合併起來。

select *
from 
(
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno, count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)e
where not exists 
(
select null
from 
(
select v.empno,v.ename,v.job,v.mgr,v.hiredate,
v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)v
 where v.empno = e.empno
 and v.ename = e.ename
 and v.job = e.job
 and v.mgr = e.mgr
 and v.hiredate = e.hiredate
 and v.sal = e.sal
 and v.deptno = e.deptno
 and v.cnt = e.cnt
 and coalesce(v.comm,0) = coalesce(e.comm,0)
 )

 union all

 select *
 from 
 (
select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
 )v
 where not exists 
 (
select null
from 
(
select e.empno,e.ename,e.job,e.mgr,e.hiredate,
e.sal,e.comm,e.deptno, count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)e
 where v.empno = e.empno
 and v.ename = e.ename
 and v.job = e.job
 and v.mgr = e.mgr
 and v.hiredate = e.hiredate
 and v.sal = e.sal
 and v.deptno = e.deptno
 and v.cnt = e.cnt
 and coalesce(v.comm,0) = coalesce(e.comm,0)
 )

其中：首先是查詢檢視e(由EMP表建立)中存在，而檢視V中不存在的資料

select *
from 
(
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno, count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
)e

where not exists 
(
select null
from 
(
select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
)v
 where v.empno = e.empno
 and v.ename = e.ename
 and v.job = e.job
 and v.mgr = e.mgr
 and v.hiredate = e.hiredate
 and v.sal = e.sal
 and v.deptno = e.deptno
 and v.cnt = e.cnt
 and coalesce(v.comm,0) = coalesce(e.comm,0)
 )

結果如下：

empnoenamejobmgrhiredatesalcommdeptnocnt
----------- ---------- --------- ----------- ----------------------- ----------- ----------- ----------- -----------
7521WARDSALESMAN76981981-02-22 00:00:00.000 1250500301
7782CLARKMANAGER78391981-06-09 00:00:00.000 2450NULL101
7839KINGPRESIDENT NULL1981-11-17 00:00:00.000 5000NULL101
7934MILLERCLERK77821982-01-23 00:00:00.000 1300NULL101

(4 行受影響)

【注意】注意，這裡比較的不是 EMP 表和檢視 V ，而是內嵌檢視 E 和內嵌檢視 V 。

同理，UNION ALL 後面的查詢語句做了相反的操作，它找出了所有存在於內嵌檢視 V 而不存在於內嵌檢視 E 的行。

如下:

select *
from (
select v.empno,v.ename,v.job,v.mgr,v.hiredate,
v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
) v
where not exists (
select null
from (
select e.empno,e.ename,e.job,e.mgr,e.hiredate,
e.sal,e.comm,e.deptno, count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,
sal,comm,deptno
) e
where v.empno = e.empno
and v.ename = e.ename
and v.job = e.job
and v.mgr = e.mgr
and v.hiredate = e.hiredate
and v.sal = e.sal
and v.deptno = e.deptno
and v.cnt = e.cnt
and coalesce(v.comm,0) = coalesce(e.comm,0)
)

empnoenamejobmgrhiredatesalcommdeptnocnt
----------- ---------- --------- ----------- ----------------------- ----------- ----------- ----------- -----------
7521WARDSALESMAN76981981-02-22 00:00:00.000 1250500302

(1 行受影響)

3.8 識別並消除笛卡兒積

問題:

你想找出部門編號為 10 的所有員工的名字及其部門所在的城市。

解決方案：

select e.ename, d.loc
from emp e, dept d
where e.deptno = 10
and d.deptno = e.deptno

分析：你也許會說這個問題好像很簡單呀！但是很多時候我們會忘記d.deptno = e.deptno 這個限制條件

記不記得我們之前說過，沒有where語句限制的連線查詢就是交叉連線，返回的結果就是笛卡爾積

【注意】

為了消除笛卡兒積，我們通常會用到n-1 法則 ，其中 n 代表 FROM 子句裡表的個數，n-1 則代表消除笛卡兒積所必需的連線查詢的最少次數。

依據表裡有什麼樣的鍵以及基於哪些列來實現表之間的連線操作，有時候必要的連線查詢次數可能會超過 n-1 次，但是當我們編寫查詢語句的時候，n-1 法則仍然是一個很好的指導原則。

3.9 組合使用連線查詢與聚合函式

問題:

你想執行一個聚合操作，但查詢語句涉及多個表。你希望確保表之間的連線查詢不會干擾聚合操作。

例如；你希望計算部門編號為 10 的員工的工資總額以及獎金總和。

在這個問題中， EMP_BONUS 表裡有如下資料。

EMP_BONUS 表中的 TYPE 列決定了獎金的數額。若 TYPE 值等於 1，則獎金為工資的 10%；若 TYPE 值等於2，則獎金為工資的 20%；若 TYPE 值等於 3，則獎金為工資的 30%。

select * from emp_bonus

EMPNO RECEIVEDTYPE
----- ----------- ----------
793417-MAR-2005 1
793415-FEB-2005 2
783915-FEB-2005 3
778215-FEB-2005 1

首先，考慮下面的查詢語句，它返回了部門編號為 10 的所有員工的工資和獎金。

select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10

結果：

empnoenamesaldeptnobonus
----------- ---------- ----------- ----------- ------------
7934MILLER130010130.0
7934MILLER130010260.0
7839KING5000101500.0
7782CLARK245010245.0

(4 行受影響)

現在我們想要計算部門編號為10 的所有員工的工資總額和獎金總額，我們能按照下面SQL語句計算嗎？

select deptno,sum(sal) as total_sal,sum(bonus) as total_bonus
from
(
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10
)X
group by deptno

結果：

deptnototal_saltotal_bonus
----------- ----------- ----------------
10100502135.0

(1 行受影響)

上面結果中total_bonus是對的，但是 total_sal並不對，因為7934號員工得了兩次獎金，所以他出現了兩次在子查詢結果中，最後在計算total_sal時，計算了兩次。

簡而言之，就是因為有部分員工多次獲得獎金，所以在 EMP 表和 EMP_BONUS 表連線之後再執行聚合函式 SUM ，就會得出錯誤的計算結果。

解決方案：

在連線查詢裡進行聚合運算時，必須十分小心才行。如果連線查詢產生了重複行，通常有兩種辦法來使用聚合函式，而且可以避免得出錯誤的計算結果。

法1：呼叫聚合函式時直接使用關鍵字DISTINCT ，這樣每個值都會先去掉重複項再參與計算；

SQL語句如下：

select deptno,
sum( distinct sal) as total_sal,
sum( bonus) as total_bonus
from
(
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10
)X
group by deptno

法2：在進行連線查詢之前先執行聚合運算（以內嵌檢視的方式），這樣可以避免錯誤的結果，因為聚合運算髮生在連線查詢之前。

SQL語句如下：

select d.deptno,
d.total_sal,
sum(e.sal*case when eb.type = 1 then .1
when eb.type = 2 then .2
else .3 end) as total_bonus
from emp e,
emp_bonus eb,
(
select deptno, sum(sal) as total_sal
from emp
where deptno = 10
group by deptno
) d
where e.deptno = d.deptno
and e.empno = eb.empno
group by d.deptno,d.total_sal

其中子查詢所得到的臨時表d：

deptnototal_sal
----------- -----------
108750

3.10 組合使用外連線查詢與聚合函式

問題:

本節的問題和 3.9 節的大致相同:計算出了部門編號為 10 的員工的工資總額和獎金總額。

但是略微修改了 EMP_BONUS 表的資料，使得部門編號為10 的員工中只有部分人獲得了獎金。

(上一題的EMP_BONUS 表有四行資料，其中一人得了兩次獎金，但是10號部門（總共就3人）的所有人都是有獎金的)

EMP_BONUS表的資料：

EMPNORECEIVEDTYPE
------- -----------------
793417-MAR-20051
793415-FEB-20052

我們依舊按照上一節的SQL語句查詢

select deptno,
sum( distinct sal) as total_sal,
sum( bonus) as total_bonus
from
(
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10
)X
group by deptno

結果是錯誤的，為什麼呢？

我們來看其中的子查詢：

select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal*case when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e ,emp_bonus eb
where e.empno=eb.empno
and e.deptno=10

結果：

empnoenamesaldeptnobonus
----------- ---------- ----------- ----------- ------------
7934MILLER130010130.0
7934MILLER130010260.0

(2 行受影響)

所以按照上一題的SQL語句來查詢，則查詢沒有計算部門編號為 10 的全部員工的工資總額，實際上只有 MILLER 的工資被計入總和，而且被錯誤地計算了兩次。

解決方案1：

下面的解決方案也和 3.9 節的類似，不同之處在於要外連線 EMP_BONUS 表，確保把部門編號為 10 的全部員工都包括進來。

selectdeptno,
sum(distinct sal) as total_sal,
sum(bonus) as total_bonus
from 
(
select e.empno,
e.ename,
e.sal,
e.deptno,
eb.type,
e.sal*case when eb.type is null then 0
when eb.type = 1 then .1
when eb.type = 2 then .2
else .3 
end as bonus
from emp e left outer join emp_bonus eb
on (e.empno = eb.empno)
where e.deptno = 10
 )X
 group by deptno

結果：

deptnototal_saltotal_bonus
----------- ----------- ------------
108750390.0

(1 行受影響)

注意其中的子查詢

select e.empno,
e.ename,
e.sal,
e.deptno,
eb.type,
e.sal*case when eb.type is null then 0
when eb.type=1 then .1
when eb.type=2 then .2
else .3
end as bonus
from emp e left outer join emp_bonus eb
on e.empno=eb.empno
where e.deptno=10

注意看：為什麼case語句比上一題中多了一個when eb.type is null then 0 ,因為有一部分的10號部門的員工是沒有獎金的，所以外連線的時候，eb.type是null

這個子查詢的結果：

empnoenamesaldeptnotypebonus
----------- ---------- ----------- ----------- ----------- -----------
7782CLARK245010NULL735.0
7839KING500010NULL1500.0
7934MILLER1300101130.0
7934MILLER1300102260.0

(4 行受影響)

解決方案2:

首先計算部門編號為 10 的員工的工資總額，然後再連線 EMP 表和 EMP_BONUS 表（這樣就避免了使用外連線）。

select d.deptno,
d.total_sal,
sum(e.sal*case when eb.type = 1 then .1
when eb.type = 2 then .2
else .3 
end) as total_bonus
from emp e,
emp_bonus eb,
(
select deptno, sum(sal) as total_sal
from emp
where deptno = 10
group by deptno
) d
where e.deptno = d.deptno
and e.empno = eb.empno
group by d.deptno,d.total_sal

3.11 從多個表中返回缺失值

問題:

你想從多個表中返回缺失值。(換言之，就是使用某種連線，將兩個表相連線，之後找到兩個表的所有相匹配的資料，包括空值)

列出所有的部門，以及該部門的所有員工（如果這個部門有員工的話）

select d.deptno,d.dname,e.ename
from dept d left outer join emp e
on (d.deptno=e.deptno)

列出所有的員工，以及該員工所屬的部門（如果這個員工有部門的話，當然我們的表中所有的員工都是有部門的，這裡僅僅是為了舉例子）

select d.deptno,d.dname,e.ename
fromdept d right outer join emp e
on (d.deptno=e.deptno)

那麼我們想要把所有的員工和部門都列出來，不論該部門是否有員工，也不論某個員工是否有部門

select d.deptno,d.dname,e.ename
from dept d left outer join emp e
on (d.deptno=e.deptno)

union 

select d.deptno,d.dname,e.ename
fromdept d right outer join emp e
on (d.deptno=e.deptno)

其實我們可以使用全連線full outer join

select d.deptno,d.dname,e.ename
fromdept d full outer join emp e
on (d.deptno=e.deptno)

【說明】

全外連線查詢其實就是合併兩個表的外連線查詢的結果集

3.12 在運算和比較中使用 Null

問題：

Null 不會等於或不等於任何值，甚至不能與其自身進行比較，但是你希望對從 Null 列返回的資料進行評估，就像評估具體的值一樣。

例如：

你想找出 EMP 表裡業務提成（ COMM列）比員工 WARD 低的所有員工。檢索結果應該包含業務提成為 Null 的員工。、

解決方案：使用使用coalesce() 函式將 Null 值替代為實際值。（coalesce 函式說明見 1.12）

coalesce 函式會返回引數列表裡的第一個非 Null 值

select ename , comm
from emp
where coalesce(comm, 0)<(select comm from emp where ename='WARD')