Joins across tables and schemas

Opening your lesson

Joins work in Snowflake as in any SQL database. JOIN ... ON keeps the rows that match in both tables, and LEFT JOIN keeps every row of the first table, with NULLs where the second has no match. That makes it the tool for finding what's missing: LEFT JOIN and then WHERE other.id IS NULL.

Short aliases keep joins readable: FROM orders o JOIN customers c ON c.customer_id = o.customer_id. When a column name is in both tables, put the alias in front of it.

Tables in different schemas, and even different databases, join just as easily. Give each its qualified name: sales.products p JOIN raw."vendor_feed" v ON v."sku" = p.sku.

SELECT o.order_id, c.first_name, c.last_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

Each order with the name of the customer who placed it.

Choose your character

Your Work Gym account