jerry的成長之路

合併語法

• Cross Joins:兩個表格交叉相乘

select *
from EMPLOYEES CROSS JOIN  DEPARTMENTS 

• Equijoins:特別注意因為DEPARTMENT_ID 兩個表格都有
  必須要告訴電腦指定哪一個表格的，不然會錯

SELECT EMPLOYEE_ID,LAST_NAME,DEPARTMENTS.DEPARTMENT_ID,LOCATION_ID
FROM EMPLOYEES JOIN DEPARTMENTS 
ON EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID

• 表格別名(Tavle Alias):可以將表格命名成另一個比較短的名字

SELECT E.EMPLOYEE_ID,E.LAST_NAME,D.DEPARTMENT_ID,D.LOCATION_ID
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

• 用ON或用WHERE來過濾額外的條件

SELECT E.EMPLOYEE_ID,E.LAST_NAME,D.DEPARTMENT_ID,D.LOCATION_ID
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
WHERE EMPLOYEE_ID =149

• ON語法-合併三個表格

SELECT EMPLOYEE_ID,CITY,DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID 
JOIN LOCATIONS L
ON D.LOCATION_ID=L.LOCATION_ID

• Non-Equijoins

SELECT E.LAST_NAME,E.SALARY ,J.GRADE_LEVEL,J.LOWEST_SAL,J.HIGHEST_SAL
FROM EMPLOYEES E JOIN JOB_GRADES J
ON E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL;

• LEFT OUTER JOIN:不管右邊有沒有，左邊都要印出來

SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.LAST_NAME,LOCATION_ID,EMPLOYEES.DEPARTMENT_ID,DEPARTMENT_NAME
FROM EMPLOYEES LEFT OUTER JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;

• RIGHT OUTER JOIN ：不管左邊有沒有，右邊都要印出來

SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.LAST_NAME,LOCATION_ID,EMPLOYEES.DEPARTMENT_ID,DEPARTMENT_NAME
FROM EMPLOYEES RIGHT OUTER JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;

• FULL OUTER JOIN:不管怎樣，是NULL都印出來

SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.LAST_NAME,LOCATION_ID,EMPLOYEES.DEPARTMENT_ID,DEPARTMENT_NAME
FROM EMPLOYEES FULL OUTER JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;

• SELF JOINS

SELECT WORKER.LAST_NAME+'   Works for   '+MANAGER.LAST_NAME
FROM EMPLOYEES WORKER JOIN EMPLOYEES MANAGER
ON WORKER.EMPLOYEE_ID=MANAGER.EMPLOYEE_ID