Oracle-多表查詢

內連線

select * from 表名 (別名) (inner) join 表名 (別名) on 連線條件

連線emp表和dep表（emp表中與dep表中的did是關聯欄位，這樣查詢did會出現2次）：

select * from emp,dep where emp.did = dep.did

相當於

select * from emp inner join dep on emp.did=dep.did

相當於

select * from emp join dep on emp.did=dep.did

如果只想出現一次關聯欄位，需要像下書寫：

select emp.*,dep.name,dep.daddress from emp join dep on emp.did=dep.did

給表指定別名

select * from 表名 別名 inner join 表名 別名 on 連線條件

-- 需要顯示的指明匹配條件，查詢結果兩個關聯列因為名稱可能不同，所以全部顯示

select * from dept d join emp e on d.deptno = e.deptno 

注意：內連線的 inner 可以省略，直接 … join … on … 預設內連線；查詢指定欄位時，如果多張表有這個欄位，需要指明是那張表的欄位

外連線

select * from 表名 (別名) left/right (outer) join 表名  (別名)  where 連線條件

左外連線

以左表為參考，左表中資料全部輸出，右表與左表資料匹配的資料才顯示

select * from emp e left outer join dep d on e.did = d.did

相當於

select * from emp e,dep d where e.did = d.did(+)

以沒有（+）的一邊為參考（這裡是emp），沒有（+）的記錄全部輸出，有（+）的記錄，只有左右表相互匹配才輸出

右外連線

以右表為參考，右表資料全部輸出，右表與左表資料匹配的資料才顯示

select * from emp e right outer join dep d on e.did = d.did

相當於

select * from emp e,dep d where e.did(+) = d.did

以沒有（+）的一邊表為參考（這裡是dep），沒有（+）的記錄全部輸出，有（+）的記錄，只有左右表相互匹配後才輸出

select * from dep d right outer join emp e on e.did = d.did

注意：外連線的 outer 可以省略，直接 … left/right join … on …

子查詢

非相關子查詢 — 單行子查詢

子查詢結果只有一行，外查詢可以直接使用 = > < 運算子進行比較
子查詢可以獨立執行 先進行子查詢再進行外查詢 子查詢不依賴於外查詢

--查得比“CR7”工資高的員工的資訊

select * from emp where salary > (select salary from emp where ename = `CR7`)

--查詢工資高於平均工資的僱員名字和工資

select ename,salary from emp where salary > (select avg(salary) from emp)

--查詢職務和CR7相同，比CR7僱傭工資高的員工

---- 查詢職務

select ejob from emp where ename = `CR7`  -

---- 查詢工資

select esalary from emp where ename = `CR7`

---- 綜合起來

select * from emp

where job = (select ejob from emp where ename = `CR7`)

and hiredate > (select esalary from emp where ename = `CR7` )

非相關子查詢 — 多行子查詢

子查詢結果有多行

外查詢進行比較需要使用 all（所有），ang（任意），in，not in

--查詢比任意一個‘java開發’工資高的員工（查詢比‘java開發’最高工資低的員工）

select * from emp

where esalary < any(select esalary from emp where ejob=`java開發`)

--相當於

select * from emp

where esalary < (select max(esalary) from emp where ejob=`java開發`)

> < ang(...) 查詢結果是 “比查出來資料中最大的小”， > ang(...) 查詢結果是  “比查出來資料中最小的大”，任意一個用ang()

--查詢比任何一個‘java開發’工資高的員工（查詢比‘java開發’最低工資低的員工）

select * from emp

where esalary < all(select esalary from emp where ejob=`java開發`)

--相當於

select * from emp

where esalary < (select min(esalary) from emp where ejob=`java開發`)

>< all(...) 查詢結果是 “比查出來資料中最小的小” ，  >all(...) 查詢結果是“比查出來資料中最大的大”，所有的用all

相關子查詢 — 子查詢依賴於外查詢

子查詢不能單獨執行
先執行外查詢再執行內查詢

--查詢本部門最高工資的員工

---- 查詢 部門 10 最高工資的 員工

select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)

---- 查詢 部門 20 最高工資的 員工

select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)

---- 查詢 部門 20 最高工資的 員工

select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30)

---->> 由上面可以知道查詢具體部門的最高工資 depno 是相同的，由此可以得到所有部門最高工資：

select * from emp e1 where sal = (select max(sal) from emp e2 where e2.deptno = e1.deptno)

select * from emp e1 where sal = (select max(sal) from emp where deptno = e1.deptno)


--查詢工資高於其所在部門的平均工資的員工

---- 查詢 10 部門員工 高於 10 部門平均工資 的員工

select * from emp where deptno = 10 and sal > (select avg(sal) from emp where deptno = 10)

---- 查詢 20 部門員工 高於 30 部門平均工資 的員工

select * from emp where deptno = 20 and sal > (select avg(sal) from emp where deptno = 20)

---- 查詢 30 部門員工 高於 30 部門平均工資 的員工

select * from emp where deptno = 30 and sal > (select avg(sal) from emp where deptno = 30)

---->> 由上面可以知道查詢具體部門的高於平局工資員工 depno 是相同的，由此可以得到所有部門高於平局工資員工：

select * from emp e1 where sal > (select avg(sal) from emp where deptno = e1.deptno)

注意：看懂了上面兩個例子就看懂了相關子查詢。

總結

–子查詢 : 一個語句包含多個select

— — 不相關子查詢： 子查詢不依賴外查詢，可以獨立執行 ， 先內後外

— — 相關子查詢： 子查詢依賴外查詢，不可以獨立執行， 先外後內

— — — 單行子查詢： 子查詢返回一行結果， 可以直接使用 = > < 等

— — — 多行子查詢： 子查詢返回多行結果， 需要藉助 in ang all

偽列 rowid rownum

—-實際表附加的列，叫偽列，像表中的列一樣但是表中不儲存

—-偽劣只用來查詢，不能進行增刪改

rowid

—- 在表中每一行資料都有一個實體地址，rowid返回該行的實體地址

—- 作用：快速定位某一行，唯一的，自動分配的，不重複

select rowid,ename,ejob from emp

rownum

—-查詢的結果集，rownum為每一行記錄標識一個行號，第一行返回1，第二行返回2 ……

—-作用：限制查詢的行數，分頁

select rownum,ename,ejob,esalary from emp

查詢員工表中前五名員工的姓名，工作，工資

select rownum,ename,ejob,esalary from emp where rownum <=5

查詢員工表中工資最高的前五名員工的姓名，工作，工資

select rownum,ename,ejob,esalary from emp where rownum <=5 order by esalary desc

----先按工資排序

select rownum,ename,ejob,esalary from emp order by esalary desc

----再進行篩選

select rownum,r.*

from (select rownum,ename,ejob,esalary from emp order by esalary desc) r

where rownum <=5


--查詢員工表中第3條到第6條員工的姓名，工作，工資

select r.r,e.ename,e.ejob,e.esalary from emp e,(select rownum r,eid from emp) r

where e.eid = r.eid and r.r between 3 and 6


select * from (select rownum r,eid,ename,ejob,esalary from emp)

where r between 3 and 6

--相當於

select * from (select rownum r,eid,ename,ejob,esalary from emp where rownum <=6)

<font size=2>注意：where r >= 3 -- 子表中 rownum 必須用別名，因為外部查詢語句不能識別內部的 rownum ，但是可以識別內部 rownum 的別名