子查詢與共用資料表運算式（CTE）

Snowflake SQL 入門

George Boorman

Senior Curriculum Manager, DataCamp

子查詢

巢狀查詢
可用於 FROM、WHERE、HAVING 或 SELECT 子句

範例：

SELECT column1 
FROM table1 
WHERE column1 = (SELECT column2 FROM table2 WHERE condition)

類型：相依與非相依子查詢

非相依子查詢

-- 主查詢回傳價格等於子查詢找到之最大值的披薩
SELECT pizza_id
FROM pizzas
-- 非相依子查詢：找出最高披薩價格
WHERE price = (
    SELECT MAX(price)
    FROM pizzas
)

子查詢不與主查詢互動

相依子查詢

子查詢會參照主查詢的欄位

SELECT pt.name, 
       pz.price, 
       pt.category
FROM pizzas AS pz
JOIN pizza_type AS pt 
    ON pz.pizza_type_id = pt.pizza_type_id
WHERE pz.price = (
  -- 找出各披薩類別的最高價格
    SELECT MAX(p2.price) -- 最高價格
    FROM pizzas AS p2
    WHERE -- 相依：使用外層查詢欄位 
      p2.pizza_type_id = pz.pizza_type_id
)

共用資料表運算式（CTE）

一般語法：

-- WITH 關鍵字
WITH cte1 AS ( -- CTE 名稱
        SELECT col_1, col_2
            FROM table1
    )
    ...
SELECT ... 
FROM cte1 -- 查詢 CTE
;

共用資料表運算式（CTE）

WITH max_price AS ( -- 名為 max_price 的 CTE
    SELECT pizza_type_id, 
           MAX(price) AS max_price
    FROM pizzas
    GROUP BY pizza_type_id
)

-- 主查詢 
SELECT pt.name, 
       pz.price, 
       pt.category
FROM pizzas AS pz
JOIN pizza_type AS pt ON pz.pizza_type_id = pt.pizza_type_id
JOIN max_price AS mp  -- 與 CTE max_price 連接
     ON pt.pizza_type_id = mp.pizza_type_id 
WHERE pz.price < mp.max_price -- 與 max_price CTE 欄位比較價格

多個 CTE

-- 以逗號分隔定義多個 CTE
WITH cte1 AS (
    SELECT ...
    FROM ...
),

cte2 AS (
    SELECT ...
    FROM ...
)

-- 主查詢結合兩個 CTE
SELECT ...
FROM cte1
JOIN cte2 ON ...
WHERE ...

為什麼用 CTE？

處理複雜操作
模組化
易讀
可重複使用

一起來練習吧！

Snowflake SQL 入門