深入了解 SQL JOINS - Mastering SQL Joins [1]

Cross-table Queries 的種類

有 4 種常用的跨表查詢的方式，分別是 INNER JOIN、OUTER JOIN、SELF JOIN,CROSS JOIN。

做資料庫查詢的時候，其實常常使用到這樣的概念，交集、差集、聯集、補集…etc，都是集合代數的運算操作。

觀念釐清

• INNER JOIN == JOIN
• LEFT JOIN == LEFT OUTER JOIN
• RIGHT JOIN == RIGHT OUTER JOIN
• FULL JOIN == FULL OUTER JOIN
• CROSS JOIN 與其他 JOIN 的行為明顯不同。與 INNER JOIN 減少選擇資料記錄相比，CROSS JOIN 則是會大幅度增加資料記錄，
• 在 SQL 語法中，OUTER 關鍵字並不是必須的，這是因為 LEFT JOIN 和 RIGHT JOIN 的存在就已經代表了外部連接的含義。
• 在多數的 SQL 引擎中，如果只寫 JOIN 預設就會使用 INNER JOIN。

實際使用中，一般情況下會直接使用 LEFT JOIN 和 RIGHT JOIN 這些較為簡潔的語法。只是某些情況下，使用 OUTER 關鍵字可以更加明確地表達出操作的意圖，尤其是當需要使用多種關聯操作進行結合時，例如 FULL OUTER JOIN、LEFT OUTER JOIN 和 INNER JOIN 結合等等。在這些情況下，使用 OUTER 關鍵字可以讓代碼更加易讀且清晰。

其他不常用的大概帶過就好，有興趣的可以自己去查查看。

• Self Join：同一 table 中進行 JOIN 操作的一種特殊情況，即將 table 自己與自己進行 JOIN，以此來獲取關聯的資料。
• Natural Join：省略 JOIN 條件的 JOIN，它會自動找到兩個 table 中具有相同名稱和資料類型的欄位，並將它們用作 JOIN 條件，以此來獲取關聯的資料。
• Equi-Join：基於相等比較運算符進行 JOIN 的方法，即將兩個 table 中具有相等值的欄位用作 JOIN 條件，以此來獲取關聯的資料。
• Non-Equi Join：基於不相等比較運算符進行 JOIN 的方法，即將兩個 table 中具有不相等值的欄位用作 JOIN 條件，以此來獲取關聯的資料。
• Theta Join：基於任意比較運算符進行 JOIN 的方法，即將兩個 table 中任意欄位用作 JOIN 條件，以此來獲取關聯的資料。
• Semi Join：只返回一個 table 中符合條件的紀錄的 JOIN，通常用於子查詢和 EXISTS 子句。
• Anti Join：只返回一個 table 中不符合條件的紀錄的 JOIN，通常用於子查詢和 NOT EXISTS 子句。

INNER JOIN

INNER JOIN 是我最常用的跨表查詢方式之一，INNER JOIN 會將兩或多個以上的 table 的資料進行聯結，並且只會顯示兩個 table 的交集。

在聯結條件使用 "ON" 關鍵字來指定，這個條件可以是兩個表之間的欄位相等，也可以是其他邏輯運算符，例如>,<等等。

簡單範例如下，假設現在有兩個 table，分別是 customers 與 orders，其中 customers 與 orders 有一個欄位是 customer_id，現在將用下面這 SQL 從 customers 和orders兩個表中取出客戶姓名、訂單日期和訂單總額：

1
2
3
4

SELECT customers.customer_name, orders.order_date, orders.order_total
FROM `customers`
INNER JOIN `orders`
ON customers.customer_id = orders.customer_id;

上述 SQL 查詢中，使用 INNER JOIN 將 customers 表和 orders 表進行聯結，聯結條件是 customers 表中的 customer_id 欄位等於 orders 表中的 customer_id 欄位。最終的查詢結果包含了所有符合聯結條件的資料行，即客戶姓名、訂單日期和訂單總額。

OUTER JOIN

LEFT JOIN / RIGHT JOIN

LEFT JOIN & RIGHT JOIN 會將兩個或多個 table 的資料進行聯結，並且只會顯示兩個 table 的交集。

• LEFT JOIN 會將左側 table (table_name1) 的所有記錄與右側 table (table_name2) 中符合連接欄位條件的記錄聯結，即使右側 table 中沒有符合的記錄，左側 table 中的記錄仍然會顯示，而右側 table 中未匹配的欄位則顯示為NULL。
• RIGHT JOIN 則一樣會會將右側 table (table_name2) 的所有記錄與左側 table (table_name1) 中符合連接欄位條件的記錄聯結，即使左側 table 中 (table_name1) 沒有符合的記錄，右側 table 中的記錄仍然會顯示，而左側 table 中未匹配的欄位則顯示為 NULL。

LEFT JOIN 範例

假設 customers table 中有以下資料：

假設 orders table 中有以下資料：

左邊的 table 為 customers，右邊的 table 為 orders，LEFT JOIN 會把 customers table 中的每一筆資料都拿來和 orders table 進行比對，如果 orders table 中有符合條件的資料就會顯示，如果 orders table 中沒有符合條件的資料就會填入 NULL。

此情況為 customers "Bob" 沒有訂單的情況，orders table 中沒有符合條件的資料，因此 Bob 的訂單相關欄位會顯示 NULL。

1
2
3
4

SELECT *
FROM customers
LEFT [OUTER] JOIN orders
ON customers.customer_id = orders.customer_id;

回傳結果就會如下：

這段 LEFT JOIN 會回傳所有 customers table 中的資料以及對應的 orders table 中的資料，但是因為 customer_id 為 2 的 “Bob” 沒有對應到 orders table 中的任何資料，所以 orders 相關欄位顯示 NULL。

RIGHT JOIN 範例

RIGHT JOIN 會把 orders table 中的每一筆資料都拿來和 customers table 進行比對，如果 customers table 中有符合條件的資料就會顯示，如果 customers table 中沒有符合條件的資料就會填入 NULL。

此情況為 order_id =4 不屬於任何客戶的情況，customers table 中沒有符合條件的資料，因此 order_id = 4 的對應 customers 相關欄位會顯示 NULL。

1
2
3
4

SELECT *
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

回傳結果：

結果中會包含 orders table 中的所有資料。
其中，第一、二行符合 JOIN 條件，所以從 customers table 中取出 customer_id 為 1 的 Alice 的資料，並將 orders table 中符合 JOIN 條件的資料也一併列出。第三行也符合 JOIN 條件，所以也將 customers table 中 customer_id 為 3 的 Charlie 的資料列出。
最後，由於 orders table 中有一筆 customer_id 是 4，而 customers table 中沒有 customer_id 是 4，所以在 JOIN 後就會產生一筆 customer_id 是 NULL 的資料。

FULL OUTER JOIN

FULL JOIN 會把 customers table 和 orders table 中的所有資料都進行比對，並將兩個 table 中符合條件的資料進行合併，如果有資料只存在於其中一個 table 中，也會一併顯示出來，如果有 table 中沒有符合條件的資料就會填入 NULL。也就是 LEFT JOIN 與 RIGHT JOIN 的聯集

假設目前有兩個 table，一個客戶(customers)table 為：

另一個訂單(orders)table 為：

customerstable 少 5、多一個 6，orderstable 少 6，多一個 5，若我們要將兩個 table 中的資料進行合併，就可以使用 FULL JOIN。

1
2
3
4

SELECT customers.Name, orders.Order_No
FROM customers
FULL JOIN orders
ON customers.C_Id=orders.C_Id;

回傳結果：

由於 FULL JOIN 會保留兩個 table 中所有的資料，因此在 customers table 中沒有對應到的資料會用 NULL 填補，同理在 orders table 中沒有對應到的資料也會用 NULL 填補。在這個範例中

• 由於 customers table 中沒有 C_Id 為 5 的客戶，沒有能對應到 Order_No 為 1257 的資料，因此 Name 欄是 NULL
• 而 orders table 中沒有對應到 C_Id 為 6 的資料(Jason 沒有訂單的情況)，因此 Jason 的 Order_No 欄是 NULL。

注意：FULL JOIN 在某些資料庫系統中可能不被支援，可以使用 UNION ALL 和 LEFT JOIN 和 RIGHT JOIN 組合使用來達到 FULL JOIN 的效果。MySQL 資料庫中就沒有 FULL JOIN，但可以用 UNION 來模擬。

UNION

當需要合併兩個或多個結構相似的表格時，就能使用 UNION 來將它們合併成一個表格。

在使用 UNION 時需注意：

• 所有 query 回傳 的 columns 數量必須相同。
• 在 query 中對應到的 columns 其資料型態必須相同。
• UNION 會移除重複的紀錄，除非使用 UNION ALL。

UNION 與 JOIN 不同之處在於，JOIN 是用於橫向結合（合併多個表格的不同欄位），而 UNION 則是用於垂直結合（合併多個表格的不同紀錄）。

假設有兩個 table 分別是 students 與 teachers，欄位資訊如下：

Students table：

Teachers table：

將這兩個表格合併成一個表格，可以使用以下的 UNION 語句：

1
2
3
4
5

SELECT id, name, age, NULL AS subject
FROM students
UNION
SELECT id, name, NULL AS age, subject
FROM teachers;

這個語句會將 students 和 teachers 這兩個表格合併成一個表格，包含 id, name, age, 和 subject 四個欄位。由於 students 表格中沒有 subject 欄位，而 teachers 表格中沒有 age 欄位，因此在 SELECT 語句中使用了 NULL 來補齊。

最後得到結果如下：

• 上述範例使用了 NULL AS 來補齊不足的欄位。這是因為在使用 UNION 時，兩個 SELECT 語句所選擇的欄位數必須相等。
• 我們想 students 和 teachers 這兩個表格合併成一個表格，但是這兩個表格中的欄位數不相等，對於欄位數不相等的情況，我們可以使用 NULL AS，將欄位數補齊。
• 在 SELECT 語句中使用 NULL AS，可以為欄位指定一個別名。上面例子中，我們使用了 NULL AS age 和 NULL AS subject 來補齊 students 和 teachers 表格中所缺少的欄位。

另一個簡單的例子，主要是為了說明 UNION 在預設情況下會移除重複的紀錄。若不想移除重複的紀錄，再用 UNION ALL。

A 公司產品 table：

B 公司產品 table：

現在我們要查出公司所有產品資料：

1
2
3

SELECT P_Name FROM products_A
UNION
SELECT P_Name FROM products_B;

結果如下：

可以看到，banana 和 apple 這兩個產品都出現了兩次，這是因為 UNION 預設會移除重複的紀錄。如果我們想要保留重複的紀錄，可以使用 UNION ALL。

1
2
3

SELECT P_Name FROM products_A
UNION ALL
SELECT P_Name FROM products_B;

結果如下：

CROSS JOIN

CROSS JOIN 其實就是對兩張表的各個紀錄做笛卡兒積 (cartesian product)，兩個 table 在結合時，不指定任何條件，即將兩個 table 中所有的可能排列組合出來，這樣會產生非常多的結果，因此 CROSS JOIN 通常不會單獨使用，而是會和其他 JOIN 一起使用。

以下這兩段 SQL Query 是相等的。

1
2
3

SELECT c.customer_id, o.order_id, o.order_date
FROM customers , orders
WHERE c.customer_id=o.customer_id;

1
2
3
4

SELECT c.customer_id, o.order_id, o.order_date
FROM customers
CROSS JOIN orders
WHERE c.customer_id=o.customer_id;

如果用上面那兩個 table 為範例執行這兩段 SQL Query，會得到 4 個客戶 * 5 個訂單，CROSS JOIN 將會回傳 20 個所有可能的排列組合結果。

SELF JOIN

SELF JOIN 是指在同一張 table 中，將 table 本身作為兩個不同的別名，並透過 JOIN 的方式將它們關聯起來

以下是一個簡單的範例，假設有一個名為 employees 的 table，其中包含員工的編號（id）、姓名（name）以及直屬上級的編號（manager_id）等欄位：

1
2
3

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

回傳結果：

上述 SQL 將 employees table 自己 JOIN 起來，並找出每位員工的直屬上級姓名。其中，e 和 m 都是 employees table 的別名，用來區分同一張 table 中的不同資料列。透過 ON 條件，我們可以讓資料庫引擎比對 e.manager_id 和 m.id 是否相等，以此找出每位員工的直屬上級。最後，SELECT 語句則會返回每位員工的姓名以及其直屬上級的姓名。

• ← Previous Post
• Next Post →