Joins i Snowflake

Introduktion till Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

JOINS

INNER JOIN
OUTER JOINS
- LEFT OUTER JOIN or LEFT JOIN
- RIGHT OUTER JOIN or RIGHT JOIN
- FULL OUTER JOIN or FULL JOIN
CROSS JOINS
SELF JOINS
NATURAL JOIN
LATERAL JOIN

Pizzadatamängd

Schemadiagram för pizzadatabasen

NATURAL JOIN

NATURAL JOIN matchar kolumner automatiskt och eliminerar dubbletter

Syntax:

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

NATURAL JOIN

Utan `NATURAL JOIN`

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

Joinresultat utan natural join

Med `NATURAL JOIN`

SELECT *
FROM pizzas AS p 
NATURAL JOIN pizza_type AS t

Joinresultat med natural join

NATURAL JOIN

❌ EJ TILLÅTET ❌

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

Bild på syntaxfel

NATURAL JOIN

✅ TILLÅTET ✅

WHERE-sats

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

LATERAL JOIN

LATERAL JOIN: låter en underfråga i FROM referera till kolumner från föregående tabeller eller vyer.

Syntax:

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

left_hand_expression – Tabell, vy eller underfråga
right_hand_expression – Inline-vy eller underfråga

LATERAL JOIN med en underfråga

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

LATERAL -- Keyword LATERAL
        ( SELECT * 
          FROM pizza_type AS t

         -- Referencing outer query column: p.pizza_type_id
          WHERE p.pizza_type_id = t.pizza_type_id

        ) AS lat

Varför LATERAL JOIN?

SELECT 
    *
FROM orders AS o,
LATERAL (
   -- Subquery calculating 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

Nu kör vi en övning!

Introduktion till Snowflake SQL

Joins i Snowflake

JOINS

Pizzadatamängd

NATURAL JOIN

NATURAL JOIN

Utan NATURAL JOIN

Med NATURAL JOIN

NATURAL JOIN

NATURAL JOIN

LATERAL JOIN

LATERAL JOIN med en underfråga

Varför LATERAL JOIN?

Nu kör vi en övning!

Utan `NATURAL JOIN`

Med `NATURAL JOIN`