Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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 ON condition, 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 TypeDescriptionResult Rows
INNER JOINReturns rows with matching keys in both tablesOnly matching rows
LEFT JOINReturns all rows from left table, matched rows from rightAll left rows + matched right rows or NULL
RIGHT JOINReturns all rows from right table, matched rows from leftAll right rows + matched left rows or NULL
FULL JOINReturns all rows when there is a match in one of the tablesAll 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.