How to use Joins in Snowflake

Are you looking to find how to use the joins within the snowflake cloud data warehouse or maybe you are looking for a solution to join two table or three tables in the Snowflake. You may also want to check what could be real-world use case scenarios where you wanted to join the tables. In some cases, you may find difficult to identify which join should be used in which situation. In this article I will take you through a step-by-step process of creating the multiple types of the join. Let’s don’t waste the time and I shall take you quickly to the code directly where I will show you the joins in Snowflake.

What are joins in Snowflake ?

Joins are used to combine the data of two or more tables. To perform join operation we need to have at least one common column that should be present in both the tables. Depending on requirement we can also join more than two tables.

Azure Data Factory Tutorial

Azure Databricks Spark Tutorial for Beginner

What are the different ways to Join tables in Snowflake

• We are having two ways to join tables. By using JOIN with ON sub-clause of the FROM clause. ( recommended way)

-- SQL Command Syntax : 
SELECT [COLUMN_NAME] FROM [TABLE_ONE] JOIN [TABLE_TWO] ON [CONDITION]

--Example : 
SELECT T1.NAME,T2.NUMBER FROM T1 JOIN T2 ON T1.ID=T2.ID

• Alternatively we can also join tables using WHERE clause.

--SQL Command Syntax : 
SELECT [COLUMN_NAME] FROM [TABLE_ONE],[TABLE_TWO] WHERE [CONDITION]

--Example : 
SELECT T1.NAME,T2.NUMBER FROM T1,T2 WHERE T1.ID=T2.ID

What are the different types of Joins in Snowflake?

There are many types of joins in snowflake as mentioned below.

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join
• Natural Join

Lets learn each and every join in detail.

How to use inner join in Snowflake?

Inner join, joins two table according to ON condition. If inner join is used without ON clause or using comma without WHERE clause then the result will be cross join. Inner join will joins the common data which should present in both the tables.

--SQL Command Syntax : 
SELECT [COLUMN_NAME] FROM [TABLE_ONE] INNER JOIN [TABLE_TWO] ON [CONDITION]

Example of Inner Join in Snowflake :

--Statement : 
SELECT  T1.ID,T1.NAME,T2.PROFESSION_DESC FROM CUSTOMER T1 INNER JOIN PROFESSION T2 ON T1.ID=T2.ID

Output :

Here we got the data of ID’s that are present in both the tables. Commonly we are having ID 1,2 on both the tables So, the output which is present below will also the representing the same.

RESULTANT TABLE

How to do a left join in snowflake in Snowflake ?

Left outer join returns all the records from the left table and the matching common records from the right table. If there is no matching records from table 2 ( right table ) with table 1 ( left table ) then there will no records retreived from the tabel 2 ( right table ).

--SQL Command Syntax : 
SELECT [COLUMN_NAME] FROM [TABLE_ONE] LEFT JOIN [TABLE_TWO] ON [CONDITION]

Left Outer Join Example :

--Statement : 
SELECT  T1.ID,T1.NAME,T2.PROFESSION_DESC FROM CUSTOMER T1 LEFT JOIN PROFESSION T2 ON T1.ID=T2.ID

Output :

Here we able to get the complete data from left table and the corresponding matching data from the right table. If there is no matching data then that value will be NULL. This can be used if we want complete data from left table and matching data from right table then we can make use of Left Outer Join.

How to perform Right Outer Join in Snowflake ?

Right outer join returns the matching common records from the left table and all the records from the right table. If there is no matching records from table 1 ( left table ) with table 2 ( right table ) then there will no records retrieved from the table 1 ( left table ).

--SQL Command Syntax : 
SELECT [COLUMN_NAME] FROM [TABLE_ONE] RIGHT JOIN [TABLE_TWO] ON [CONDITION]

Example of Right Outer Join in Snowflake :

--Statement : 
SELECT  T1.ID,T1.NAME,T2.PROFESSION_DESC FROM CUSTOMER T1 RIGHT JOIN PROFESSION T2 ON T1.ID=T2.ID

Output :

Here we able to get the corresponding matching data from the left table and the complete data from right table. If there is no matching data then that value will be NULL.

How to use Full Outer Join in Snowflake ?

Full outer join returns the matching common records as well as all the records from both the tables. If there is no matching records from table 1 ( left table ) and table 2 ( right table ) then there will be corresponding NULL values. For example each table has a row that doesn’t have matching row in the other table then the output contains two rows with NULL values.

--SQL Command Syntax : 
SELECT [COLUMN_NAME] FROM [TABLE_ONE] FULL OUTER JOIN [TABLE_TWO] ON [CONDITION]

Example of Full Outer Join in Snowflake :

--SQL Statement : 
SELECT  T1.ID,T1.NAME,T2.PROFESSION_Desc FROM Customer T1 FULL OUTER JOIN Profession T2 ON T1.ID=T2.ID

Output :

Here we able to get the corresponding matching data from the left table and right table as well as the non-matching rows from the both the tables. If there is non-matching data then accordingly that value will be NULL.

How to perform Cross Join in Snowflake ?

When each rows of table 1 is combined with each row of table 2 then this is known as cross join or cartesian join. Because of cartesian product, any conditions will not be allows. But we can make use of filtering operations ( WHERE Condition ). In other words, cross join with condition is actually a kind of inner join.

--Syntax : 
SELECT [COLUMN_NAME] FROM [TABLE_ONE] CROSS JOIN [TABLE_TWO]

Cross Join in Example in Snowflake:

--SQL Statement : 
SELECT  T1.ID,T1.NAME,T2.PROFESSION_DESC FROM Customer T1 CROSS JOIN  Profession T2

Output :

As we know the result will be cartesian product which means each row ( table 1 ) will be multiplied with each row of another table ( table 2 ) as the same thing shown below.

How to use Natural Join in snowflake ?

Natural Join is used to join two tables without any condition. It is same as Inner Join but, the difference is Inner join needs condition where, as Natural join doesn’t require any condition. Natural join automatically joins the tables by detecting the common columns for comparison. Here both tables need same column name with same data type for the join to apply.

--SQL Command Syntax : 
SELECT [COLUMN_NAME] FROM [TABLE_ONE] NATURAL JOIN [TABLE_TWO]

Natural Join Example in Snowflake :

--Statement : 
SELECT  T1.ID,T1.NAME,T2.PROFESSION FROM T1 NATURAL JOIN T2

Output :

Here both the tables have same column name with same data type. Commonly we are having column name ID which contains IDs 1 and 2. Natural join automatically joins both the tables as a result we get the output below as same as inner join.

Lateral Join in Snowflake

We also have one more join which is not mentioned above i.e.. Lateral Join. Lateral Join mostly behaves like a correlated sub-query when compared with other joins. It acts like a server executed the loop. For example we are having two tables. If each row in left table is executing the sub-query which is right table then this is known as Lateral Join.

Snowflake official Documentation Link

Conclusion :

By this, we have reached the end of our insightful article on how to make use of joins with examples in Snowflake task.  In this article, we have learned what are the different types of joins that can be used. In this blog we learned the usage of each join and its statement. For few joins there will be no need of condition to be applied. I hope this article helped you for getting the information in detail regarding joins.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

Revanth Kumar