SQL Joins

概覽

Inner Join

A、B 共同資料。

SELECT *
FROM A
INNER JOIN B ON A.KEY = B.KEY

Left Join

只要 A 部分資料（排除與 B 的交集）。

SELECT *
FROM A
LEFT JOIN B ON A.KEY = B.KEY
WHERE B.KEY IS NULL

Right Join

只要 B 部分資料（排除與 A 的交集）。

SELECT *
FROM A
RIGHT JOIN B ON A.KEY = B.KEY
WHERE A.KEY IS NULL

Full Outer Join

所有資料（A ∪ B）

SELECT *
FROM A
FULL OUTER JOIN B ON A.KEY = B.KEY

只有 A 或只有 B 的資料（排除交集）

SELECT *
FROM A
FULL OUTER JOIN B ON A.KEY = B.KEY
WHERE A.KEY IS NULL OR B.KEY IS NULL

Natural Join

自動以相同欄位名稱進行 JOIN，不需明確指定 ON 條件。

語法

SELECT *
FROM A
NATURAL JOIN B

範例

使用 employee 與 department 表（共同欄位：DEPARTMENT_ID）：

SELECT e.FULL_NAME, d.DEPARTMENT_NAME
FROM employee e
NATURAL JOIN department d

Inner Join 等效寫法

SELECT e.FULL_NAME, d.DEPARTMENT_NAME
FROM employee e
INNER JOIN department d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID

Natural Join 會自動匹配所有相同名稱的欄位，可能導致非預期的結果。建議明確使用 ON 條件。

Demo Tables

department table

DEPARTMENT_ID	DEPARTMENT_NAME
1	Executive
2	HR
3	Sales
4	Development
5	Support
6	Research

employee table

EMPLOYEE_ID	FULL_NAME	DEPARTMENT_ID	ROLE	MANAGER_ID
1	John Smith	1	CEO	(null)
2	Sarah Goodes	1	CFO	1
3	Wayne Ablett	1	CIO	1
4	Michelle Carey	2	HR Manager	1
5	Chris Matthews	3	Sales Manager	2
6	Andrew Judd	4	Development Manager	3
7	Danielle McLeod	5	Support Manager	3
8	Matthew Swan	2	HR Representative	4
9	Stephanie Richardson	2	Salesperson	5
10	Tony Grant	3	Salesperson	5
11	Jenna Lockett	4	Front-End Developer	6
12	Michael Dunstall	4	Back-End Developer	6
13	Jane Voss	4	Back-End Developer	6
14	Anthony Hird	(null)	Support	7
15	Natalie Rocca	5	Support	7

location table

LOCATION_ID	OFFICE_NAME	CITY	COUNTRY
1	Headquarters	New York	USA
2	Main Office	London	UK
3	Sales Office	Tokyo	Japan
4	Development Office	San Francisco	USA
5	Support Office	Sydney	Australia
6	Research Office	Berlin	Germany

Join 3 Tables

多表關聯查詢，串接 employee、department 與 location 表。

情境

查詢員工姓名、所屬部門、及辦公室位置。

範例

SELECT
    e.FULL_NAME,
    e.ROLE,
    d.DEPARTMENT_NAME,
    l.OFFICE_NAME,
    l.CITY,
    l.COUNTRY
FROM employee e
INNER JOIN department d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
INNER JOIN location l ON d.DEPARTMENT_ID = l.LOCATION_ID

結果範例

FULL_NAME	ROLE	DEPARTMENT_NAME	OFFICE_NAME	CITY	COUNTRY
John Smith	CEO	Executive	Headquarters	New York	USA
Sarah Goodes	CFO	Executive	Headquarters	New York	USA
Wayne Ablett	CIO	Executive	Headquarters	New York	USA
Michelle Carey	HR Manager	HR	Main Office	London	UK
Matthew Swan	HR Representative	HR	Main Office	London	UK
Chris Matthews	Sales Manager	Sales	Sales Office	Tokyo	Japan
Tony Grant	Salesperson	Sales	Sales Office	Tokyo	Japan

使用 LEFT JOIN

若要包含沒有部門或位置的員工：

SELECT
    e.FULL_NAME,
    e.ROLE,
    d.DEPARTMENT_NAME,
    l.OFFICE_NAME
FROM employee e
LEFT JOIN department d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
LEFT JOIN location l ON d.DEPARTMENT_ID = l.LOCATION_ID

使用 LEFT JOIN 時，沒有匹配的欄位會顯示 NULL。例如 Anthony Hird 的 DEPARTMENT_ID 為 NULL，因此 DEPARTMENT_NAME 和 OFFICE_NAME 都會是 NULL。