BigQuery 的聯結（JOIN）

BigQuery 入門

Matthew Forrest

Field CTO

在 BigQuery 進行資料聯結

INNER JOIN：兩個資料表同時存在的值。
LEFT JOIN：左表全部列，搭配右表相符列。
RIGHT JOIN：右表全部列，搭配左表相符列。
FULL JOIN：兩表的所有列，含相符與不相符。
CROSS JOIN：兩表的每一列與每一列兩兩配對。

以視覺方式呈現 SQL 聯結

聯結的真實情境範例

Customers：左表

Orders：右表

INNER JOIN：只顯示有下單的客戶與其訂單
LEFT JOIN：顯示所有客戶，就算沒有下過單
RIGHT JOIN：顯示所有訂單，就算缺少客戶 ID
FULL JOIN：顯示所有客戶與所有訂單，就算彼此未對應
CROSS JOIN：不加條件，將每筆訂單配對到每位客戶

INNER JOIN

只回傳兩個資料集的相符結果

SELECT
  c.customer_id, s.product_name
FROM customers c
-- The INNER keyword is optional
JOIN sales_data s
ON c.customer_id = s.customer_id;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 2           | Running Shoes        |

LEFT JOIN

回傳 LEFT 資料集的所有列

SELECT
  c.customer_id, s.product_name
FROM customers c
LEFT JOIN sales_data s
ON c.customer_id = s.customer_id;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 2           | Running Shoes        |
| 3           | null                 |

RIGHT JOIN

回傳 RIGHT 資料集的所有列

SELECT
  c.customer_id, s.product_name
FROM customers c
RIGHT JOIN sales_data s
ON c.customer_id = s.customer_id;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 2           | Running Shoes        |
| null        | External Microphone  |

OUTER JOIN

「右加左」聯結：RIGHT 與 LEFT 兩資料集的所有列

SELECT
  c.customer_id, s.product_name
FROM customers c
OUTER JOIN sales_data s
ON c.customer_id = s.customer_id;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 2           | Running Shoes        |
| 3           | null                 |
| null        | External Microphone  |

SELF 或 CROSS JOIN

笛卡兒聯結：每列對上每列

SELECT
  c.customer_id,
  s.product_name,

-- Adding table names separated 
-- by a comma is a CROSS JOIN
-- Order is determined by the
-- left table, here "customers"
FROM customers c, sales_data s;

| customer_id | product_name         |
|-------------|----------------------|
| 1           | Bluetooth Headphones |
| 1           | null                 |
| 2           | Bluetooth Headphones |
| 2           | null                 |
| 3           | null                 |
| 3           | Bluetooth Headphones |

JOIN 與 UNNEST

也可用來聯結展平（unnest）後的資料

SELECT
  c.customer_id,
  payments.method
FROM customers c, 
UNNEST(
  customers.payment_methods
) payments;

| customer_id | product_name |
|-------------|--------------|
| 1           | Visa         |
| 1           | Mastercard   |
| 1           | Venmo        |
| 1           | Paypal       |
| 2           | Amex         |
| 2           | Visa         |

一起來練習吧！

BigQuery 入門