SQLBolt：https://sqlbolt.com/lesson/introduction

• SQL Lesson 7: OUTER JOINs

使用概念 & 語法

• OUTER JOIN：LEFT JOIN RIGHT JOIN FULL JOIN

Like the INNER JOIN these three new joins have to specify which column to join the data on.

When joining table A to table B, a LEFT JOIN simply includes rows from A regardless of whether a matching row is found in B. The RIGHT JOIN is the same, but reversed, keeping rows in B regardless of whether a match is found in A. Finally, a FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.

• 語法結構：

/* Select query with LEFT/RIGHT/FULL JOINs on multiple tables */
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table 
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

SQL Lesson 7: OUTER JOINs

我們一樣直接開始解題。

寫下去的話，就會發現題目相當循序漸進的引導，讓我們一步一步理解呢。

題目 1

-- 1. Find the list of all buildings that have employees
SELECT DISTINCT building FROM employees;

題目 2

-- 2. Find the list of all buildings and their capacity
SELECT * FROM buildings;

題目 3

/* 3. List all buildings and the distinct employee roles in each building (including empty buildings) */
SELECT DISTINCT building_name, role 
FROM buildings LEFT JOIN employees
ON building_name = building;

題解

LEFT JOIN （左外部合併查詢）

是在合併兩個資料表中，取回左邊資料表的所有紀錄，就算在右邊資料表沒有存在合併欄位的值，顯示結果會以左邊資料表為主。

SELECT [「想搜尋的欄位(資料表A & B都可)]
FROM [資料表A] LEFT JOIN [資料表B] 
ON [資料表A].[關聯鍵] =[資料表B].[關聯鍵]

左邊是指在 LIFT JOIN 左邊的資料表名稱，也就是 [資料表A]。

RIGHT JOIN （右外部合併查詢）

是在合併兩個資料表中，取回右邊資料表的所有紀錄，就算在左邊資料表沒有存在合併欄位的值，顯示結果會以右邊資料表為主。

SELECT 想搜尋的欄位(資料表A & B都可) 
FROM [資料表A] RIGHT JOIN [資料表B] 
ON [資料表A].[關聯鍵] = [資料表B].[關聯鍵]

右邊是指在 RIGHT JOIN 右邊的資料表名稱，也就是 [資料表B]。

FULL JOIN（完全外部合併查詢）

可以取回左右兩邊資料表的所有紀錄。

SELECT 想收尋的欄位(資料表A & B都可) 
FROM [資料表A] FULL JOIN [資料表B] 
ON [資料表A].[關聯鍵] = [資料表B].[關聯鍵]

左右兩邊的資料表，這裡是指 [資料表A] & [資料表B]

參考資料：

• SQL Lesson 7: OUTER JOINs
• [iT鐵人賽Day16] SQL語法-表與表之間的關係 Join 也可使用小精靈產生Join（捲毛蔡）
• MySQL 學習筆記