Laterally a new move

In Snowflake, you can do something akin to an APPLY, but not. That, my friends, is a LATERAL join. According to Snowflake documentation, this type of join “allows an inline view to reference columns from the table expression that precedes the inline view.”

Yes, that’s gibberish. Let’s show what you’re doing.

To test this out, I took 2 tables from the AdventureWorks database – 2014 edition – that our friends at Microsoft came out with years ago – the HumanResources.Department and HumanResources.Employee tables – and placed them into Snowflake.

When I run my query with a LATERAL join, I get the following:

Notice that LATERAL is not quite the equivalent of an APPLY because there is a comma after the “departments” table in our query instead of a new line to reference the actual JOIN.

It may actually help to remember by doing it this way – with the INNER JOIN listed in the query:

Just to make life even more interesting – and who doesn’t need that, right?

Let’s do something we’ve all seen done, but know that we shouldn’t do it. (Who puts this stuff out there if we all know better?)

We’re going to put an array of data into a project_names field of employees.

UPDATE employees 
SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'UDFs') 
WHERE mod(employee_id, 2) = 1;
    
UPDATE employees 
SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'Lateral Joins')
WHERE mod(employee_id, 2) = 0;

Yes, we set all the even employee_id to have 1 value, the odds another. (Yes, two functions for the price of 1 with this blog! You now have an idea to use that versatile function mod!)

And now we can use some of the information that I gave you in my last blog post about flatten:

I can now use that nasty array data in a format that works nicely in databases – multiple rows (with fewer rows of data to boot – keeping your database sizes small).

And with that my friends, we have come to the end of yet another blog post. Using lateral and flatten can help you out with some of your database woes. But using lateral by itself, can make some of your APPLYs that much easier – especially when you can’t actually use the APPLY operator.