學習筆記專區

--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