在 Snowflake 中进行连接

Snowflake SQL 入门

George Boorman

Senior Curriculum Manager, DataCamp

连接（JOIN）

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

披萨数据集

披萨数据库架构图

NATURAL JOIN

NATURAL JOIN 会自动匹配同名列并去除重复列

语法：

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

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 的连接结果

NATURAL JOIN

❌ 不允许 ❌

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

语法错误图片

NATURAL JOIN

✅ 允许 ✅

WHERE 子句

SELECT *
FROM pizzas AS p 
NATURAL JOIN pizza_type AS t
WHERE pizza_type_id = 'bbq_ckn'

LATERAL JOIN

LATERAL JOIN：允许 FROM 中的子查询引用前面表或视图的列。

语法：

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

left_hand_expression - 表、视图或子查询
right_hand_expression - 内联视图或子查询

带子查询的 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

为何使用 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 中进行连接

连接（JOIN）

披萨数据集

NATURAL JOIN

NATURAL JOIN

不使用 NATURAL JOIN

使用 NATURAL JOIN

NATURAL JOIN

NATURAL JOIN

LATERAL JOIN

带子查询的 LATERAL JOIN

为何使用 LATERAL JOIN？

开始练习吧！

不使用 `NATURAL JOIN`

使用 `NATURAL JOIN`