# 05 連結兩個或多個表格資料的 JOIN

## LEFT JOIN

LEFT JOIN 是以左表格作爲**主表格**，右表格則是**從表格**

`product_category 1->* product_category`

SELECT *
FROM product
    LEFT JOIN product_category
        ON product.product_category_id = product_category.product_category_id


重複的欄位名稱：product_category_id

### 爲表格取別名

SELECT p.product_id,
       p.product_name,
       pc.product_category_id,
       pc.product_category_name
FROM product AS p
    LEFT JOIN product_category AS pc
        ON p.product_category_id = pc.product_category_id
ORDER BY pc.product_category_name,
         p.product_name

### JOIN 同一個表格

## RIGHT JOIN


RIGHT JOIN 是以右表格作爲**主表格**，左表格則是**從表格**

SELECT *
FROM product
    RIGHT JOIN product_category 
        ON product.product_category_id = product_category.product_category_id


> table1 LEFT JOIN table2 是否等同於 table2 RIGHT JOIN table1?

是

## INNER JOIN


LEFT JOIN

RIGHT JOIN

INNER JOIN

## 篩選連結資料時常見的陷阱

SELECT *
FROM customer AS c
    LEFT JOIN customer_purchases AS cp
        ON c.customer_id = cp.customer_id
WHERE cp.customer_id > 0

> 有 WHERE 子句跟沒有 WHERE 子句差別在哪？

沒有購買記錄的 customer 會被篩除
所以如果希望看到右邊表格沒有對應的資料也可以回傳 NULL，那就不要對右邊表格做篩選

---

現在想要寫一個查詢，回傳所有除了 2019-04-03 以外購買的顧客清單（包含尚未有購買記錄的）

SELECT c.*, cp.market_date
FROM customer AS c
  LEFT JOIN customer_purchases AS cp
    ON c.customer_id = cp.customer_id
WHERE cp.market_date <> '2019-04-03'

這樣會有什麼問題？

問題1: 沒有購買記錄的顧客會被篩除


解法：

SELECT c.*, cp.market_date
FROM customer AS c
  LEFT JOIN customer_purchases AS cp
    ON c.customer_id = cp.customer_id
WHERE (cp.market_date <> '2019-04-03' OR cp.market_date IS NULL)


問題2: 同一位顧客的名字會出現很多次

解法：

SELECT DISTINCT c.*
FROM customer AS c
  LEFT JOIN customer_purchases AS cp
    ON c.customer_id = cp.customer_id
WHERE (cp.market_date <> '2019-04-03' OR cp.market_date IS NULL)

## JOIN 兩個以上的表格


A LEFT JOIN B, B LEFT JOIN C

SELECT
  b.booth_number,
  b.booth_type,
  vba.market_date,
  v.vendor_id,
  v.vendor_name,
  v.vendor_type
FROM booth AS b
  LEFT JOIN vendor_booth_assignments AS vba
    ON b.booth_number = vba.booth_number
  LEFT JOIN vendor AS v
    ON v.vendor_id = vba.vendor_id
ORDER BY b.booth_number, vba.market_date

A LEFT JOIN B
A LEFT JOIN C