昨天介紹了如何使用 SELECT 語法去查詢我們的資料表，並使用 WHERE 子句去給予條件，精準的查詢到我們所需要的資料，但是大家有發現了嗎，我們現在做的查詢都僅限定於一個資料表，但在實際的狀況中，我們幾乎總是會使用到多個相互關聯的資料表。這種關聯性是關聯式資料庫（RDBMS）的一個核心特點，他可以讓我們從多個資料表中組合資料，進行更為複雜和全面的數據分析

所以今天，今天的主題將集中在如何進行交叉查詢（Joins）和彙總查詢（Aggregation），以更全面地利用關聯式資料庫的優勢

• 首先就先來建立我們的兩張資料表

1. books 資料表中有：id, title, author_id, price 欄位
2. authors 資料表中有： id, name 欄位

-- 先建立 authors table
CREATE TABLE authors (
    id SERIAL PRIMARY KEY NOT NULL,
    name varchar(255) NOT NULL
);


-- 並建立一些資料
INSERT INTO authors (name) 
values ('張三'), ('李四'), ('王五');

-- 建立 books table
CREATE TABLE books (
    id SERIAL PRIMARY KEY NOT NULL
    title varchar(255),
    author_id REFERENCES authors(id),
    price INT
)

-- 一樣寫入一些資料
INSERT INTO books (title, author_id, price) 
values ('如來神掌', 1, 99), ('獨孤九劍', 1, 150), ('降龍十八掌', 2, 199), ('輕功水上飄', 3, 299) ('無名寶典', null, 9999);

• 這邊只要先照做就好了，之後我們都會介紹到什麼是 SERIAL PRIMARY KEY REFERENCES

所以我們的兩張資料表會看起來像下面這樣

authors 資料表

books 資料表

交叉查詢：Joins

INNER JOIN

INNER JOIN 算是最基本也最重要的交叉查詢方式，可以找到兩張資料表共同存在的欄位的資料(row)，簡單來說就是找到這兩張資料表的關聯性，如果有關聯的話就會回傳到最後的查詢結果，反之沒有關聯的話就不會回傳

(應該看下面這張圖會比較好懂)

情境：我們想要知道這些作者們撰寫哪些書籍

-- 這邊使用 AS 重新命名我們的回傳結果

SELECT books.id, authors.name AS author_name, books.title AS book_title
FROM books
INNER JOIN authors 
ON authors.id = books.author_id;

結果：

 id | author_name | book_title
----+-------------+------------
  1 | 張三        | 如來神掌
  2 | 張三        | 獨孤九劍
  3 | 李四        | 降龍十八掌
  4 | 王五        | 輕功水上飄


-- 可以看到 作者：趙六 和 書本：無名寶典 沒有出現在最後的查詢結果
-- 因為這兩者都沒有在兩張 table 產生關聯
(4 rows)

• 其實你也可以這樣寫

SELECT books.id, authors.name AS author_name, books.title AS book_title
FROM authors, books
WHERE authors.id = books.author_id;

LEFT JOIN (或 LEFT OUTER JOIN)

LEFT JOIN (或 LEFT OUTER JOIN)，則是會以左側的資料表為主，找到有關聯於兩張資料表的資料時會出現在最後的查詢結果中，若是沒有的話會在對應的欄位會是 NULL

• 情境：我們想要找到所有在 books table 上的書，並將 作者名稱呈現出來，若是不知道作者是誰也沒差

-- 依照情境敘述來說，我們是要找到所有的書，所以這時我們的左側資料表會放上 books 

SELECT books.id AS book_id, books.title AS book_title, authors.name AS author_name
FROM books  -- 左側資料表
LEFT JOIN authors 
ON books.author_id = authors.id;

結果：

 book_id | book_title | author_name
---------+------------+-------------
       1 | 如來神掌   | 張三
       2 | 獨孤九劍   | 張三
       3 | 降龍十八掌 | 李四
       4 | 輕功水上飄 | 王五
       5 | 無名寶典   |

(5 rows)

RIGHT JOIN (或 RIGHT OUTER JOIN)

剛剛介紹完了 LEFT JOIN，其實 RIGHT JOIN 是差不多的意思，只是是以右側的資料表為主

所以其實我們可以將剛剛上一個情境搬來用，只要將順序 table 的順序對調，效果會是一樣的

SELECT books.id AS book_id, books.title AS book_title, authors.name AS author_name
FROM authors  -- 調換兩張資料表的位置
RIGHT JOIN books 
ON books.author_id = authors.id;

結果：

 book_id | book_title | author_name
---------+------------+-------------
       1 | 如來神掌   | 張三
       2 | 獨孤九劍   | 張三
       3 | 降龍十八掌 | 李四
       4 | 輕功水上飄 | 王五
       5 | 無名寶典   |

(5 rows)

FULL JOIN (或 FULL OUTER JOIN)

在 SQL 查詢中的使用上較為少見，這個查詢結果會回傳兩個資料表中所有的資料(row)，即使其中一個資料表中沒有與另一張資料表有關聯性的話，還是會回傳，但會是 NULL。

SELECT *
FROM books
FULL JOIN authors
ON books.author_id = authors.id;

 id |   title    | author_id | price | id | name
----+------------+-----------+-------+----+------
  1 | 如來神掌   |         1 |    99 |  1 | 張三
  2 | 獨孤九劍   |         1 |   150 |  1 | 張三
  3 | 降龍十八掌 |         2 |   199 |  2 | 李四
  4 | 輕功水上飄 |         3 |   299 |  3 | 王五
  5 | 無名寶典   |           |  9999 |    |
    |           |          |       |  4 | 趙六

(6 rows)

彙總查詢：Aggregation

彙總查詢指的是能夠把多個資料列的資料經過計算，產生單一結果的功能。舉例來說， count、sum、avg（平均值）、max（最大值）、min（最小值）都是彙總查詢的函式。

1. COUNT(): 計算資料表中有多少筆資料

• 想知道 books table 中有多少筆資料

SELECT COUNT(*) FROM books;

結果：

 count
-------
     5

(1 row)

2. SUM(): 計算資料表中某欄位的總和

• 想知道 books table 中有所有書的價格加總

SELECT SUM(price) FROM books;

結果：

  sum
-------
 10746

(1 row)

3. AVG(): 計算資料表中某欄位的平均值

• 想知道 books table 中有所有書的的平均價格

-- AVG() 函數返回的是 numeric 型態
-- ROUND() 函數取小數點後兩位

SELECT ROUND(AVG(price), 2) AS avg FROM books;

結果：

   avg
---------
 2149.20

(1 row)

4. MAX(), MIN(): 找出資料表中某欄位的最小和最大值。

SELECT MIN(price), MAX(price) FROM books;

結果：

 min | max
-----+------
  99 | 9999

(1 row)

結語:

今天介紹了很重要的 JOIN 語法，因為我們時常會使用的很多都是 RDBMS ，而它最大的特點就是關聯性，所以在兩張資料表上交互查詢是很平常的事情了

明天將會補完 更新 Update & 刪除 Delete 資料，並做小小的回故

感謝大家