Execute An Update Using a Left Join in Snowflake

In this article, we will cover a very specific use case. We’ll be looking at how to perform a table update but, more specifically, we will be looking at how to update a table using a left join in Snowflake.

Join types

There are several types of joins in SQL. The two most commonly used are inner join and left join.

Inner join

An inner join takes all the records from one table and matches them to any records with a match in the other table.  The resulting data set only includes the records that match. 

Left Join

A left join takes all the records from one table and matches them to any records with a match similar to an inner join, but a left join  keeps all the records from the table on the “left” side  of the join and returns null whenever there is no match on the “right” side of the join.

Left join table update

In this article, we’re going to be looking at performing a table update, meaning we are going to update the values in one table using another table’s values. However, we want to update the table with a null value whenever there is no match in the table used to make updates, which is what makes this particular use case fairly uncommon but necessary in specific cases.

If you would like to follow along with the examples, run this code to create the tables used in the example. To follow along, run this code  before each example  to reset the tables to their original content  for each example :

create table if not exists table_y (join_column int, column_y varchar);

truncate table_x;

truncate table_y;

insert into table_x (join_column, column_x)

    values

    (1, 'in stock'), (2, 'on order');

insert into table_y (join_column, column_y)

    values

    (1, 'on order');

Update with a left join in where clause

Now that we have the problem set up let’s take a look at the first solution. Snowflake has an update command with the following syntax.

update table_x

set column_x = column_y

from table_y

where table_x.join_column = table_y.join_column

A join in the where clause defaults to an inner join. A left join needs to be used, or else any of the records in table_x that do not have a match in table_y will not be updated at all. The records with no match need to be updated with NULL. Left joins can be used in the where clause in Snowflake using a special syntax. However, this feature is only recommended when you are migrating existing code.

To create a left join using a where clause in Snowflake, add (+) to the end of the table that you want to keep all records for. Here’s the same update as above, but with a left join. The only difference is the last

update table_x

set column_x = column_y

from table_y

where table_x.join_column = table_y.join_column (+)

A more optimal solution

Snowflake doesn’t recommend using joins in the where clause if it can be avoided. Joins made in the from clause are much more robust and are the recommended method for Snowflake.

The first solution is very common in transactional databases. While this is the most optimal solution for row-based databases, because Snowflake is a columnar data store, there is a more optimal solution. In the second solution, a temporary data set is created using a subquery with a left join in the from clause, and then that temporary data set is used for the update.

update table_x

set column_x = column_y

from (

select 

table_x.join_column, 

            column_y

from 

table_x

left join table_y

on table_x.join_column = table_y.join_column

) as table_y

where table_x.join_column = table_y.join_column

Wrap-up

Table updates can get complicated, especially because table updates alter data in your database. This can lead to inaccurate data.

With Datameer, best practices in data warehouse management are built-in to the data transformation engine so that you don’t have to worry about writing or execution of table updates that might accidentally corrupt your data.