Different Snowflake Join Types and Examples

SQL Join is a clause in your query that is used for combining specific fields from two or more tables based on the common columns available. Snowflake joins are different from the set operators. Joins are used to combine rows from multiple tables. In this article, we will learn about different Snowflake join types with some examples.

Test Data

Following tables will be used to demonstrate different join types available in Snowflake cloud data warehouse system.

S_STUDENTS
+----+------+-----------+
| ID | NAME | CITY      |
|----+------+-----------|
|  1 | AAA  | London    |
|  3 | CCC  | Bangalore |
|  4 | DDD  | Mumbai    |
|  5 | EEE  | Bangalore |
+----+------+-----------+

S_DEPT
+-----+---------+-----+
| DID | NAME    | SID |
|-----+---------+-----|
| 100 | CS      |   1 |
| 101 | Maths   |   1 |
| 102 | Physics |   2 |
| 103 | Chem    |   3 |
+-----+---------+-----+

Different Snowflake Join Types

Following are Different Redshift Join Types

[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
CROSS JOIN
NATURAL JOIN

JOIN or INNER JOIN

JOIN or INNER JOIN It returns the matching rows from both the tables. Inner join is most commonly used in primary-foreign key relation tables.

Syntax:

SELECT table1.col1, table2.col1 
FROM table2 
JOIN table1 
ON (table1.common_col = table2. common_col);

Results:

select s.id, s.name, d.name 
from s_students as s
join s_dept as d
on (s.id = d.sid);
+----+------+-------+
| ID | NAME | NAME  |
|----+------+-------|
|  1 | AAA  | CS    |
|  1 | AAA  | Maths |
|  3 | CCC  | Chem  |
+----+------+-------+

LEFT JOIN or LEFT OUTER JOIN

The left outer join returns all rows from the left table even if there is no matching row in the right table. The unmatched records from right tables will be NULL in the result set.

Syntax:

SELECT table1. col1, table1.col2, table2.col1, table2.col2
FROM table1 
LEFT OUTER JOIN table2 
ON (table1.matching_col = table2.matching_col);

Result:

select s.id, s.name, d.name 
from s_students as s
left outer join s_dept as d
on (s.id = d.sid);
+----+------+-------+
| ID | NAME | NAME  |
|----+------+-------|
|  1 | AAA  | CS    |
|  1 | AAA  | Maths |
|  3 | CCC  | Chem  |
|  4 | DDD  | NULL  |
|  5 | EEE  | NULL  |
+----+------+-------+

RIGHT JOIN or RIGHT OUTER JOIN

The right outer join returns all rows from the right table even if there is no matching row in the left table. The unmatched records from left tables will be NULL in the result set.

Syntax:

SELECT table1. col1, table1.col2, table2.col1, table2.col2
FROM table1 
RIGHT OUTER JOIN table2 
ON (table1.matching_col = table2.matching_col);

Result:

select s.id, s.name, d.name 
from s_students as s
right outer join s_dept as d
on (s.id = d.sid);
+----+------+---------+
| ID | NAME | NAME    |
|----+------+---------|
|  1 | AAA  | CS      |
|  1 | AAA  | Maths   |
|  3 | CCC  | Chem    |
| NULL | NULL | Physics |
+----+------+---------+

FULL JOIN or FULL OUTER JOIN

The full outer join returns all rows from the both tables that fulfill the JOIN condition. The unmatched rows from both tables will be NULL.

Syntax:

SELECT table1. col1, table1.col2, table2.col1, table2.col2
FROM table1 
FULL OUTER JOIN table2 
ON (table1.matching_col = table2.matching_col);

Results:

select s.id, s.name, d.name 
from s_students as s
full outer join s_dept as d
on (s.id = d.sid);
+----+------+---------+
| ID | NAME | NAME    |
|----+------+---------|
|  1 | AAA  | CS      |
|  1 | AAA  | Maths   |
| NULL | NULL | Physics |
|  3 | CCC  | Chem    |
|  4 | DDD  | NULL    |
|  5 | EEE  | NULL    |
+----+------+---------+

CROSS JOIN

The cross join produces a result set with all combinations of rows from the left and right tables. The cross join will degrade the performance.

Syntax:

SELECT table1. col1, table1.col2, table2.col1, table2.col2
FROM table1 
CROSS JOIN table2;

Results:

select s.id, s.name, d.name 
from s_students as s
cross join s_dept as d;
+----+------+---------+
| ID | NAME | NAME    |
|----+------+---------|
|  1 | AAA  | CS      |
|  3 | CCC  | CS      |
|  4 | DDD  | CS      |
|  5 | EEE  | CS      |
|  1 | AAA  | Maths   |
|  3 | CCC  | Maths   |
|  4 | DDD  | Maths   |
|  5 | EEE  | Maths   |
|  1 | AAA  | Physics |
|  3 | CCC  | Physics |
|  4 | DDD  | Physics |
|  5 | EEE  | Physics |
|  1 | AAA  | Chem    |
|  3 | CCC  | Chem    |
|  4 | DDD  | Chem    |
|  5 | EEE  | Chem    |
+----+------+---------+

NATURAL JOIN

A natural join is identical to an explicit JOIN on the common columns of the two tables, except that the common columns are included only once in the output.

For examples, following example uses natural keyword to perform inner join.

select s.id, s.name, d.name 
from s_students as s
natural join s_dept as d;

Note that, you should use natural join only if you have common column.

Hope this helps 🙂