Introduction to Snowflake SQL
George Boorman
Senior Curriculum Manager, DataCamp
NATURAL JOIN
automatically match columns and eliminate duplicated onesSyntax:
SELECT ...
FROM <table_one> [
{
| NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
}
]
JOIN <table_two>
[ ... ]
NATURAL JOIN
SELECT *
FROM pizzas AS p
JOIN pizza_type AS t
ON t.pizza_type_id = p.pizza_type_id
NATURAL JOIN
SELECT *
FROM pizzas AS p
NATURAL JOIN pizza_type AS t
❌ NOT ALLOWED ❌
select *
FROM pizzas AS p
NATURAL JOIN pizza_type AS t
ON t.pizza_type_id = p.pizza_type_id
$$
✅ ALLOWED ✅
WHERE
clauseSELECT *
FROM pizzas AS p
NATURAL JOIN pizza_type AS t
WHERE pizza_type_id = 'bbq_ckn'
LATERAL JOIN
: lets a subquery in FROM
reference columns from preceding tables or views.Syntax:
SELECT ...
FROM <left_hand_expression> , --
LATERAL
(<right_hand_expression>)
left_hand_expression
- Table, view, or subquery
right_hand_expression
- Inline view or subquery
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
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
Introduction to Snowflake SQL