SQL | 語法基礎篇 (2)

繼之前的 SQL CRUD、運算子、函式之後，今天來寫 SQL 很常用到的指令 JOIN。JOIN的功能呢，就是建立資料表之間的聯繫。

在前一篇文章裡，已經建立了三個資料表：drinks、customers、orders。接下來會說明如何用JOIN讓他們產生關聯。

JOIN

為什麼會需要JOIN？我們已知在 orders 資料表裡記錄了每個飲料品項的販賣情況，但這個資料表中只記錄了飲料的 drinks_id，並沒有飲料的詳細名稱，詳細名稱是記錄在 drinks 資料表裡面，所以會需要用JOIN來幫忙兩個資料表做連結。

INNER JOIN

所謂INNER JOIN，就是查尋兩邊資料表都有的資料，比方說，現在要把 orders 的 drinks_id 對回 drinks 的 id 來找出飲料名稱，那就是這樣寫：

SELECT orders.`drinks_id`, orders.`amounts`, drinks.`name`
FROM drinks
JOIN orders
ON drinks.`id` = orders.`drinks_id`;

於是乎我們得到下面這個結果，現在知道我們賣出的飲料名稱了：

所以JOIN的重點在於：

1. SELECT，反正查詢一定要有SELECT
2. FROM是原本的資料表
3. JOIN是要加入查詢的資料表
4. ON則是參考點

LEFT JOIN 和 RIGHT JOIN

從上述可以看到，INNER JOIN為我們列出了兩邊資料相符的部分，但如果今天想把參考點對應不到的資料也一併呈現呢？比方說我還是想列出所有飲料名稱怎麼辦，那就是LEFT JOIN 和 RIGHT JOIN 上場的時候啦！

在這裡，LEFT指的是FROM後面的資料表，RIGHT指的是JOIN後面的資料表，這樣講可能不太清楚，看 code 和結果就會清晰很多：

// LEFT JOIN
SELECT orders.`drinks_id`, orders.`amounts`, drinks.`name`
FROM drinks
LEFT JOIN orders
ON drinks.`id` = orders.`drinks_id`;

// RIGHT JOIN
SELECT orders.`drinks_id`, orders.`amounts`, drinks.`name`
FROM orders
RIGHT JOIN drinks
ON drinks.`id` = orders.`drinks_id`;​

上述兩種寫法差別在 drinks 資料表所在的位置，但都會得到下圖這個結果：

SELF JOIN

這玩意兒有點邪門，自己和自己JOIN是啥鬼？其實這種情況會用在像下面這種在同一張資料表做比較的狀況。

比方說，我們要在 drinks 中找比冰咖啡便宜的飲料：

SELECT d1.`name`, d2.`name` AS `cheaper_name`, d2.price
FROM drinks AS d1
JOIN drinks AS d2
ON d1.price > d2.price
WHERE d1.`name` = '冰咖啡';

綜合練習一下

1. 哪項飲料銷售量最高？

SELECT drinks.`name`, SUM(orders.`amounts`) AS `total`
FROM drinks
JOIN orders
ON drinks.`id` = orders.`drinks_id`
GROUP BY orders.`drinks_id`
ORDER BY `total` DESC
LIMIT 1;

2. 誰是購買額最高的顧客？

SELECT customers.`name`, SUM(orders.`amounts` * drinks.`price`) AS `revenue`
FROM orders
JOIN customers
ON customers.`id` = orders.`customers_id` 
JOIN drinks
ON drinks.`id` = orders.`drinks_id`
GROUP BY customers.`id`
ORDER BY `revenue` DESC
LIMIT 1;

SQL Transaction

這是一個補充知識~ 什麼是 Transaction (交易)？

SQL 作為操縱資料的語言，有時難免會遇到複數資料操縱必須全部執行完成，否則乾脆不要執行的時候，比如說：轉帳時 A、B 帳戶的資料。

若是今天 A 轉了錢給 B，那他們的帳戶金額資料勢必得更新嘛。但當 A 更新完後，程式突然崩潰了，B 沒更新到帳戶資料，這樣算有入帳還是沒入帳？所以會需要交易來幫助管理需要全部執行完畢的步驟來避免這種情形。

操作就像這樣：

set autocommit = 0;
START TRANSACTION;
UPDATE ... SET ... WHERE account = "A";
UPDATE ... SET ... WHERE account = "B";
COMMIT;