在 Snowflake 進行連接

Snowflake SQL 入門

George Boorman

Senior Curriculum Manager, DataCamp

JOINS

  • INNER JOIN
  • OUTER JOINS
    • LEFT OUTER JOIN 或 LEFT JOIN
    • RIGHT OUTER JOIN 或 RIGHT JOIN
    • FULL OUTER JOIN 或 FULL JOIN
  • CROSS JOINS
  • SELF JOINS
  • NATURAL JOIN
  • LATERAL JOIN
Snowflake SQL 入門

Pizza 資料集

披薩資料庫架構圖

Snowflake SQL 入門

NATURAL JOIN

  • NATURAL JOIN 會自動比對同名欄位並移除重複欄位。

語法：

SELECT ...
FROM <table_one> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                     }
                   ]
                   JOIN <table_two>
[ ... ]
Snowflake SQL 入門

NATURAL JOIN

不用 NATURAL JOIN

SELECT * 
FROM pizzas AS p 
JOIN  pizza_type AS t 
    ON t.pizza_type_id = p.pizza_type_id

未使用 natural join 的連接結果

使用 NATURAL JOIN

SELECT *
FROM pizzas AS p 
NATURAL JOIN pizza_type AS t

  使用 natural join 的連接結果

Snowflake SQL 入門

NATURAL JOIN

 

❌ 不允許 ❌

select *
FROM pizzas AS p 
NATURAL JOIN pizza_type AS t
    ON  t.pizza_type_id = p.pizza_type_id

語法錯誤畫面

Snowflake SQL 入門

NATURAL JOIN

$$

✅ 允許 ✅

  • 可加上 WHERE 子句
SELECT *
FROM pizzas AS p 
NATURAL JOIN pizza_type AS t
WHERE pizza_type_id = 'bbq_ckn'
Snowflake SQL 入門

LATERAL JOIN

  • LATERAL JOIN：讓 FROM 裡的子查詢可參照前面資料表或檢視的欄位。

語法：

SELECT ...
FROM <left_hand_expression> , -- 
LATERAL 
(<right_hand_expression>)

  • left_hand_expression－資料表、檢視或子查詢

  • right_hand_expression－內嵌檢視或子查詢

Snowflake SQL 入門

搭配子查詢的 LATERAL JOIN

SELECT 
    p.pizza_id, 
    lat.name, 
    lat.category 
FROM pizzas AS p,

LATERAL -- 關鍵字 LATERAL
        ( SELECT * 
          FROM pizza_type AS t

         -- 參照外層查詢欄位：p.pizza_type_id
          WHERE p.pizza_type_id = t.pizza_type_id

        ) AS lat
Snowflake SQL 入門

為什麼用 LATERAL JOIN？

SELECT 
    *
FROM orders AS o,
LATERAL (
   -- 計算 total_spent 的子查詢
    SELECT 
        SUM(p.price * od.quantity) AS total_spent
    FROM order_details AS od
    JOIN pizzas AS p 
          ON od.pizza_id = p.pizza_id
    WHERE o.order_id = od.order_id
) AS t
ORDER BY o.order_id
Snowflake SQL 入門

一起來練習吧！

Snowflake SQL 入門

Preparing Video For Download...