學習筆記專區

--ON語法─合併三個表格

join 跟on是一組，不可拆

select EMPLOYEE_ID,city,DEPARTMENT_NAME--EMPLOYEE_ID(EMPLOYEES)、city(LOCATIONS)、DEPARTMENT_NAME(DEPARTMENTS)

from EMPLOYEES e join DEPARTMENTS d --擷取這兩個資料表，並給予別名

on d.DEPARTMENT_ID = e.DEPARTMENT_ID--篩選條件：當兩方相等時(where d.DEPARTMENT_ID = e.DEPARTMENT_ID)

join LOCATIONS L--加入擷取新資料表

on d.LOCATION_ID = L.LOCATION_ID--篩選條件：當兩方相等時(d.LOCATION_ID = L.LOCATION_ID)

order by EMPLOYEE_ID

--↑↓一樣

select EMPLOYEE_ID,city,DEPARTMENT_NAME

from EMPLOYEES e, DEPARTMENTS d, LOCATIONS L

where d.DEPARTMENT_ID = e.DEPARTMENT_ID

and d.LOCATION_ID = L.LOCATION_ID

order by EMPLOYEE_ID

 

---

EMPLOYEE_ID	city	DEPARTMENT_NAME
100	Seattle	Executive
101	Seattle	Executive
102	Seattle	Executive
103	Southlake	IT
104	Southlake	IT
107	Southlake	IT
124	South San Francisco	Shipping
141	South San Francisco	Shipping
142	South San Francisco	Shipping
143	South San Francisco	Shipping
144	South San Francisco	Shipping
149	Oxford	Sales
174	Oxford	Sales
176	Oxford	Sales
200	Seattle	Administration
201	Toronto	Marketing
202	Toronto	Marketing
205	Seattle	Accounting
206	Seattle	Accounting

 

---