SQL 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兩個表中取出客戶姓名、訂單日期和訂單總額：

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。

回傳結果就會如下：

這段 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。

回傳結果：

結果中會包含 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。

回傳結果：

由於 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 語句：

這個語句會將 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：

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

結果如下：

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

結果如下：

CROSS JOIN

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

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

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

SELF JOIN

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

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

回傳結果：

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