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 的别名