Out Joins三種類型:
- left outer joins:會顯示出關鍵字左邊的表格有、右邊的表格是Null
- right outer joins:會顯示出關鍵字右邊的表格有、左邊的表格是Null
- full outer joins:只要一邊有,就算另一邊沒有也會顯示
outer可略,例如"left outer joins"寫成"left joins"
--left outer join
select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME--所需資料欄位
from EMPLOYEES e left outer join DEPARTMENTS d --1.打出所需資料表,左邊有資料時不論右邊有沒有都回傳
on e.DEPARTMENT_ID = d.DEPARTMENT_ID--3.條件設定:只要兩者相等的資料(預設唯有兩邊資料表都有對應資料才會回傳,但是outer會改變此點)
--4.因為1.的設定是左邊有資料不論右邊有沒有都要回傳,所以有Grant
e.LAST_NAME | e.DEPARTMENT_ID | d.DEPARTMENT_NAME |
King | 90 | Executive |
Kochhar | 90 | Executive |
De Haan | 90 | Executive |
Hunold | 60 | IT |
Ernst | 60 | IT |
Lorentz | 60 | IT |
Mourgos | 50 | Shipping |
Rajs | 50 | Shipping |
Davies | 50 | Shipping |
Matos | 50 | Shipping |
Vargas | 50 | Shipping |
Zlotkey | 80 | Sales |
Abel | 80 | Sales |
Taylor | 80 | Sales |
Grant | NULL | NULL |
Whalen | 10 | Administration |
Hartstein | 20 | Marketing |
Fay | 20 | Marketing |
Higgins | 110 | Accounting |
Gietz | 110 | Accounting |
--right outer join
select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME--2.取出這兩個表格中的這三欄,
from EMPLOYEES e right outer join DEPARTMENTS d--1.取出這兩個表格
on e.DEPARTMENT_ID = d.DEPARTMENT_ID--3.取出的三欄中,有一欄(DEPARTMENT_ID)篩選成是兩個表格的共通項目,藉此聯繫兩邊
--因為設定是右邊(d.DEPARTMENT_ID)有資料不論左邊有沒有都要回傳,所以雖然共同欄目中e.DEPARTMENT_ID沒有190但因為0d.DEPARTMENT_ID有,所以還是有Contracting
e.LAST_NAME | e.DEPARTMENT_ID | d.DEPARTMENT_NAME |
Whalen | 10 | Administration |
Hartstein | 20 | Marketing |
Fay | 20 | Marketing |
Mourgos | 50 | Shipping |
Rajs | 50 | Shipping |
Davies | 50 | Shipping |
Matos | 50 | Shipping |
Vargas | 50 | Shipping |
Hunold | 60 | IT |
Ernst | 60 | IT |
Lorentz | 60 | IT |
Zlotkey | 80 | Sales |
Abel | 80 | Sales |
Taylor | 80 | Sales |
King | 90 | Executive |
Kochhar | 90 | Executive |
De Haan | 90 | Executive |
Higgins | 110 | Accounting |
Gietz | 110 | Accounting |
NULL | NULL | Contracting |
--full outer join
select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME
from EMPLOYEES e full outer join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
--因為設定是兩邊有資料不論另邊有沒有都要回傳,所以有Contracting跟Grant都有
e.LAST_NAME | e.DEPARTMENT_ID | d.DEPARTMENT_NAME |
King | 90 | Executive |
Kochhar | 90 | Executive |
De Haan | 90 | Executive |
Hunold | 60 | IT |
Ernst | 60 | IT |
Lorentz | 60 | IT |
Mourgos | 50 | Shipping |
Rajs | 50 | Shipping |
Davies | 50 | Shipping |
Matos | 50 | Shipping |
Vargas | 50 | Shipping |
Zlotkey | 80 | Sales |
Abel | 80 | Sales |
Taylor | 80 | Sales |
Grant | NULL | NULL |
Whalen | 10 | Administration |
Hartstein | 20 | Marketing |
Fay | 20 | Marketing |
Higgins | 110 | Accounting |
Gietz | 110 | Accounting |
NULL | NULL | Contracting |
留言列表