How to Use Join in Snowflake: Syntax and Examples
In Snowflake, you use
JOIN to combine rows from two or more tables based on a related column between them. The basic syntax is SELECT ... FROM table1 JOIN table2 ON condition, where JOIN can be INNER, LEFT, RIGHT, or FULL depending on the result you want.Syntax
The basic syntax for a join in Snowflake is:
- SELECT: Choose columns to display.
- FROM: The first table.
- JOIN: The type of join (INNER, LEFT, RIGHT, FULL).
- ON: The condition that matches rows between tables.
sql
SELECT columns FROM table1 JOIN_TYPE JOIN table2 ON table1.column = table2.column;
Example
This example shows how to join two tables employees and departments to get employee names with their department names using an INNER JOIN.
sql
CREATE OR REPLACE TABLE employees (id INT, name STRING, dept_id INT); CREATE OR REPLACE TABLE departments (id INT, dept_name STRING); INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Carol', 10); INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'); SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id;
Output
NAME | DEPT_NAME
-------|-----------
Alice | Sales
Bob | Engineering
Carol | Sales
Common Pitfalls
Common mistakes when using joins in Snowflake include:
- Forgetting the
ONcondition, which causes a Cartesian product (all rows combined). - Using the wrong join type, leading to missing or extra rows.
- Not qualifying column names when columns have the same name in both tables.
sql
/* Wrong: Missing ON condition causes all rows to combine */ SELECT * FROM employees e JOIN departments d; /* Right: Always specify ON condition */ SELECT * FROM employees e JOIN departments d ON e.dept_id = d.id;
Quick Reference
| Join Type | Description | Result Rows |
|---|---|---|
| INNER JOIN | Returns rows with matching keys in both tables | Only matching rows |
| LEFT JOIN | Returns all rows from left table, matched rows from right | All left rows + matched right rows or NULL |
| RIGHT JOIN | Returns all rows from right table, matched rows from left | All right rows + matched left rows or NULL |
| FULL JOIN | Returns all rows when there is a match in one of the tables | All rows from both tables, NULL where no match |
Key Takeaways
Always use the ON clause to specify how tables relate when joining.
Choose the join type (INNER, LEFT, RIGHT, FULL) based on which rows you want to keep.
Qualify column names when they appear in both tables to avoid confusion.
Missing ON condition causes a large, unintended result set (Cartesian product).
Test joins with small data sets to understand their behavior before using on large tables.
