In this post we are going to cover LATERAL in Snowflake.
A subquery in the FROM clause cannot see the row the outer query is working on. It is a table expression, so it is worked out on its own, and there is no current row for it to read. LATERAL is the one word that changes that. Put it in front of the subquery and the subquery is evaluated once for each outer row, and it can see that row.
We will start with the query that fails, add the word, and watch it run. Then we will find that the same join quietly lost two customers, try the pattern most people come to LATERAL for and get an error from Snowflake instead, write that question properly with QUALIFY, and finish with LATERAL FLATTEN, which is the form you will write most often.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 33 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's start with the tables we are going to use
- Let's try a subquery in the FROM clause
- Now let's add the word LATERAL
- Did we get every customer back?
- Now let's try the famous use of LATERAL
- So how do we do top N per group in Snowflake?
- Now let's use LATERAL for what it is good at here
- Let's finish with the LATERAL you will use most: FLATTEN
- So when do we use it, and what do we use instead?
Let's start with the tables we are going to use
Everything on this page queries two of the shared sample tables for this course. CUSTOMERS holds one row per customer, keyed by CUSTOMER_ID, with a CUSTOMER_NAME. ORDERS holds one row per order, with its own ORDER_ID, an AMOUNT, and the CUSTOMER_ID of whoever placed it.
The last section adds EVENTS_JSON, which has a semi-structured PAYLOAD column.
Create them once with the course setup script, then point your worksheet at them.
-- Level 3 runs against the shared sample tables for this course.
-- This episode uses CUSTOMERS and ORDERS, joined on CUSTOMER_ID, plus
-- EVENTS_JSON in the last section. Create them once with the course
-- setup script, then point your worksheet at them:
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;This episode captured no SELECT * of CUSTOMERS, ORDERS or EVENTS_JSON, so you will not find a raw grid of any of them below, and this page makes no claim about how many rows they hold. Every count quoted is read off a grid that is on the page, including the customer total, which one of the queries asks Snowflake for directly rather than asking you to trust it.
Let's try a subquery in the FROM clause
We will start with the query that does not work, because the reason it does not work is the whole reason LATERAL exists.
The idea is reasonable enough. Put CUSTOMERS C in the FROM clause, put a subquery next to it, and have the subquery filter ORDERS down to C.CUSTOMER_ID, the customer the outer query is currently on.
SELECT C.CUSTOMER_NAME, T.ORDER_ID, T.AMOUNT
FROM CUSTOMERS C,
(SELECT ORDER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID = C.CUSTOMER_ID) T
ORDER BY C.CUSTOMER_ID;000904 (42000): SQL compilation error: error line 3 at position 62 invalid identifier 'C.CUSTOMER_ID'
invalid identifier, and the identifier it names is C.CUSTOMER_ID. Snowflake is not being difficult. As far as that subquery is concerned, C does not exist.
A subquery in the FROM clause is evaluated independently of the rows beside it. It is a table expression, not a function of the current row, so there is no “current row” for it to read.
A subquery in a SELECT list or a WHERE clause can correlate to the outer query. One in FROM cannot, and that is the gap.
Now let's add the word LATERAL
Here is the same query again. The select list is the same, and the subquery is character-for-character the same, correlation to C.CUSTOMER_ID included. We have put the word LATERAL in front of the subquery.
One other thing changed, and it is not what makes the query work. We added T.ORDER_ID to the ORDER BY as a tie-breaker, so each customer's orders come out in a stable order rather than whatever order Snowflake feels like.
SELECT C.CUSTOMER_NAME, T.ORDER_ID, T.AMOUNT
FROM CUSTOMERS C,
LATERAL (SELECT ORDER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID = C.CUSTOMER_ID) T
ORDER BY C.CUSTOMER_ID, T.ORDER_ID;| CUSTOMER_NAME | ORDER_ID | AMOUNT |
|---|---|---|
| Northwind Traders | 1001 | 4200.00 |
| Northwind Traders | 1003 | 6150.00 |
| Northwind Traders | 1006 | 5020.00 |
| Northwind Traders | 1011 | 8100.00 |
| Northwind Traders | 1017 | 5540.00 |
| Northwind Traders | 1023 | 6730.00 |
| Northwind Traders | 1029 | 7460.00 |
| Northwind Traders | 1035 | 8820.00 |
| Northwind Traders | 1040 | 9640.00 |
| Blue Ridge Supply | 1002 | 890.50 |
| Blue Ridge Supply | 1008 | 1120.00 |
| Blue Ridge Supply | 1014 | 750.00 |
It runs. The subquery was evaluated once for each row of CUSTOMERS, and each time it could see that customer's CUSTOMER_ID.
That is all LATERAL means. This table expression may refer to the rows that come before it in the FROM clause.
The order we write them in matters, because “before” is doing real work in that sentence. Our lateral subquery can see C only because C is listed first.
The comma between C and the subquery is a join. Written out it is a CROSS JOIN, which Episode 27 covered. What we get here is not a Cartesian product, though, because a lateral subquery produces different rows for each outer row. The right side is a different set of rows each time round.
Did we get every customer back?
Now the catch, and it is the kind that does not announce itself. Let's ask the same question a different way. Of all the customers in the table, how many came back from that join?
SELECT COUNT(DISTINCT C.CUSTOMER_ID) AS CUSTOMERS_RETURNED,
(SELECT COUNT(*) FROM CUSTOMERS) AS CUSTOMERS_TOTAL
FROM CUSTOMERS C,
LATERAL (SELECT ORDER_ID FROM ORDERS WHERE CUSTOMER_ID = C.CUSTOMER_ID) T;| CUSTOMERS_RETURNED | CUSTOMERS_TOTAL |
|---|---|
| 8 | 10 |
Eight of the ten. CUSTOMERS_RETURNED counts the distinct customers the lateral join produced. CUSTOMERS_TOTAL is a scalar subquery over CUSTOMERS in the same result, so the two numbers are measured in the same breath and neither has to be taken on trust.
Two customers have placed no orders. For each of them the subquery returned nothing, and a comma LATERAL behaves like an inner join. No rows from the right side means no row in the result at all. The customer disappears, with no error and no NULL to mark the spot.
This is the failure mode worth remembering, because the query that loses the rows looks exactly like the query that keeps them. If you are using a lateral subquery to decorate a list of customers, check your row count against the source table before you believe the result.
Hold that thought. It changes further down the page, and the thing that changes it is not the join.
Now let's try the famous use of LATERAL
Most people meet LATERAL while looking up top N per group. The biggest two orders for each customer, the latest three events per device, that shape of question.
In PostgreSQL the idiom is a lateral subquery with an ORDER BY and a LIMIT inside it. It is a genuinely elegant pattern. Let's run it here.
SELECT C.CUSTOMER_NAME, T.ORDER_ID, T.AMOUNT
FROM CUSTOMERS C,
LATERAL (SELECT ORDER_ID, AMOUNT FROM ORDERS
WHERE CUSTOMER_ID = C.CUSTOMER_ID
ORDER BY AMOUNT DESC NULLS LAST LIMIT 2) T
ORDER BY C.CUSTOMER_ID;002031 (42601): SQL compilation error: Unsupported subquery type cannot be evaluated
Unsupported subquery type cannot be evaluated. That is Snowflake's own wording, captured as it came back, and it is the message you will land on if you port that pattern across without changing it.
Let's try the LEFT JOIN LATERAL spelling instead
The usual next move is to reach for LEFT JOIN LATERAL ... ON TRUE, which in other databases both keeps the empty groups and sidesteps the comma join.
SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME, T.ORDER_ID
FROM CUSTOMERS C
LEFT JOIN LATERAL (SELECT ORDER_ID FROM ORDERS
WHERE CUSTOMER_ID = C.CUSTOMER_ID LIMIT 1) T ON TRUE
ORDER BY C.CUSTOMER_ID;002031 (42601): SQL compilation error: Unsupported subquery type cannot be evaluated
The same error.
Two queries on this page failed with that message, and both of them had a LIMIT inside a correlated LATERAL. The correlated lateral subquery without a LIMIT, the one further up this page, ran without complaint.
That is what happened in this capture run. It is not a documented rule, and this page is not going to promote it into one. Treat it as a place to look first when you meet Unsupported subquery type, not as a specification.
So how do we do top N per group in Snowflake?
With QUALIFY, from Episode 23. We join the two tables normally, number the rows within each customer with ROW_NUMBER(), and let QUALIFY filter on that number.
A WHERE clause cannot do that, because the window function has not been evaluated yet when WHERE runs.
SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME, O.ORDER_ID, O.AMOUNT
FROM CUSTOMERS C
LEFT JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
QUALIFY ROW_NUMBER() OVER (PARTITION BY C.CUSTOMER_ID
ORDER BY O.AMOUNT DESC NULLS LAST) = 1
ORDER BY C.CUSTOMER_ID;| CUSTOMER_ID | CUSTOMER_NAME | ORDER_ID | AMOUNT |
|---|---|---|---|
| 101 | Northwind Traders | 1040 | 9640.00 |
| 102 | Blue Ridge Supply | 1028 | 1240.00 |
| 103 | Kestrel Analytics | 1031 | 4025.00 |
| 104 | Harbour Foods | 1036 | 2540.00 |
| 105 | Alpine Logistics | 1026 | 8340.00 |
| 106 | Delta Print Co | 1027 | 1690.00 |
| 107 | Meridian Health | 1037 | 13100.00 |
| 108 | Copper Kettle Ltd | NULL | NULL |
| 109 | Vantage Media | 1038 | 5930.00 |
| 110 | Sable Interiors | NULL | NULL |
One row per customer, and every customer is here, including the two that have no orders at all. They carry NULL in ORDER_ID and AMOUNT. That is the LEFT JOIN doing its job, and it is exactly what the lateral version above could not give us.
Why NULLS LAST is in there
Have a look at Delta Print Co. Its top order in this grid is 1027, at 1690.00. Now look back at the correlated lateral grid further up the page. Delta Print Co also has order 1041, and its AMOUNT is NULL.
So one of the amounts inside that customer's partition is unknown, sitting beside four real ones. Under a plain DESC Snowflake sorts NULLs first, which would have made the unknown row the top order for that customer. NULLS LAST is what put the largest real amount first instead.
That is worth writing every time the join can manufacture a NULL or the data can carry one. Here it is not decoration. It is the difference between 1027 and 1041 in the grid above.
Now let's ask for the top two per customer
We widen the predicate from = 1 to <= 2.
SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME, O.ORDER_ID, O.AMOUNT
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
QUALIFY ROW_NUMBER() OVER (PARTITION BY C.CUSTOMER_ID
ORDER BY O.AMOUNT DESC NULLS LAST) <= 2
ORDER BY C.CUSTOMER_ID, O.AMOUNT DESC;| CUSTOMER_ID | CUSTOMER_NAME | ORDER_ID | AMOUNT |
|---|---|---|---|
| 101 | Northwind Traders | 1040 | 9640.00 |
| 101 | Northwind Traders | 1035 | 8820.00 |
| 102 | Blue Ridge Supply | 1028 | 1240.00 |
| 102 | Blue Ridge Supply | 1008 | 1120.00 |
| 103 | Kestrel Analytics | 1031 | 4025.00 |
| 103 | Kestrel Analytics | 1024 | 3910.00 |
| 104 | Harbour Foods | 1036 | 2540.00 |
| 104 | Harbour Foods | 1005 | 2310.75 |
| 105 | Alpine Logistics | 1026 | 8340.00 |
| 105 | Alpine Logistics | 1007 | 7890.00 |
| 106 | Delta Print Co | 1027 | 1690.00 |
| 106 | Delta Print Co | 1018 | 1430.00 |
| 107 | Meridian Health | 1037 | 13100.00 |
| 107 | Meridian Health | 1032 | 12850.00 |
| 109 | Vantage Media | 1038 | 5930.00 |
| 109 | Vantage Media | 1030 | 5180.00 |
Read that query carefully, because two things changed, not one. The predicate widened, and the join went from LEFT JOIN back to a plain JOIN.
So the two customers with no orders are not in this grid. An inner join dropped them again. What we have is eight customers with orders, two rows each.
If we want the empty customers kept as well, we put the LEFT back. The point of showing it this way is that the join type and the window predicate are independent decisions, and changing one while meaning to change the other is an easy way to lose rows.
This is the query LATERAL could not write, and it is worth noticing that it needs no subquery in the FROM clause at all.
Now let's use LATERAL for what it is good at here
If the headline use is out, what is left? A lateral subquery is at its best when we want a calculation evaluated once per outer row. A per-customer total, a count, a running figure. Expressed in one place instead of spread across a join and a GROUP BY.
SELECT C.CUSTOMER_NAME, S.N_ORDERS, S.TOTAL
FROM CUSTOMERS C,
LATERAL (SELECT COUNT(*) AS N_ORDERS, SUM(AMOUNT) AS TOTAL
FROM ORDERS WHERE CUSTOMER_ID = C.CUSTOMER_ID) S
ORDER BY C.CUSTOMER_ID;| CUSTOMER_NAME | N_ORDERS | TOTAL |
|---|---|---|
| Northwind Traders | 9 | 61660.00 |
| Blue Ridge Supply | 6 | 6060.50 |
| Kestrel Analytics | 5 | 15635.25 |
| Harbour Foods | 4 | 9076.25 |
| Alpine Logistics | 5 | 36565.00 |
| Delta Print Co | 4 | 3760.00 |
| Meridian Health | 5 | 56800.00 |
| Copper Kettle Ltd | 0 | NULL |
| Vantage Media | 3 | 15870.00 |
| Sable Interiors | 0 | NULL |
Every customer is in that result, Copper Kettle Ltd and Sable Interiors included, each with 0 orders and a NULL total.
So why did nobody get dropped this time?
That should stop us, because the join is the same one that lost two customers earlier on the page. Same comma, same LATERAL, same correlation. Opposite behaviour.
The difference is inside the subquery. An aggregate with no GROUP BY always returns exactly one row. Even when it matched nothing, in which case COUNT(*) is 0 and SUM is NULL. There is no such thing as an empty result from that subquery, so there is nothing for the inner join to drop.
| What the lateral subquery returns | Customers in the result | Why |
|---|---|---|
| Rows, one per matching order | 8 of 10, from the two-value count earlier on this page | Two customers matched nothing, so the subquery returned no rows and the comma join dropped them |
An aggregate, with no GROUP BY | 10 of 10, countable in the grid just above | The subquery cannot return zero rows, so every outer row survives |
The join did not decide that. The subquery did. Whether a comma LATERAL preserves our outer rows depends entirely on whether the thing inside it can come back empty, and that is not visible in the join syntax at all.
And for this particular query, we do not need LATERAL
Let's be honest about the example. A plain LEFT JOIN with a GROUP BY answers the same question.
SELECT C.CUSTOMER_NAME, COUNT(O.ORDER_ID) AS N_ORDERS, SUM(O.AMOUNT) AS TOTAL
FROM CUSTOMERS C
LEFT JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
GROUP BY C.CUSTOMER_NAME, C.CUSTOMER_ID
ORDER BY C.CUSTOMER_ID;| CUSTOMER_NAME | N_ORDERS | TOTAL |
|---|---|---|
| Northwind Traders | 9 | 61660.00 |
| Blue Ridge Supply | 6 | 6060.50 |
| Kestrel Analytics | 5 | 15635.25 |
| Harbour Foods | 4 | 9076.25 |
| Alpine Logistics | 5 | 36565.00 |
| Delta Print Co | 4 | 3760.00 |
| Meridian Health | 5 | 56800.00 |
| Copper Kettle Ltd | 0 | NULL |
| Vantage Media | 3 | 15870.00 |
| Sable Interiors | 0 | NULL |
Identical output, row for row and value for value. So the rule is the ordinary one. Reach for the simpler construct when it works.
LATERAL earns its place when the per-row calculation is awkward to express as a grouped join. Several aggregates over different filters, or a subquery we want to read as one unit. Not merely because it is available.
Two things differ between those last two queries even though the output does not. COUNT(*) inside the lateral subquery counts rows of ORDERS, while COUNT(O.ORDER_ID) in the grouped version counts non-NULL order IDs. That is why the grouped version gives 0 rather than 1 for a customer whose only row was manufactured by the LEFT JOIN. Write COUNT(*) there and the empty customers would report one order each.
Let's finish with the LATERAL you will use most: FLATTEN
There is one more form, and in practice it turns up in real Snowflake code far more often than anything above.
FLATTEN is a table function. It takes a semi-structured value, such as an array inside a VARIANT column, and returns one row per element. It has to be lateral, because the array it expands comes from the row it is running for.
SELECT E.EVENT_ID, F.VALUE:sku::VARCHAR AS SKU, F.VALUE:qty::INT AS QTY
FROM EVENTS_JSON E,
LATERAL FLATTEN(INPUT => E.PAYLOAD:items) F
ORDER BY E.EVENT_ID, SKU;| EVENT_ID | SKU | QTY |
|---|---|---|
| 1 | A-100 | 8 |
| 1 | B-220 | 4 |
| 2 | C-310 | 3 |
| 3 | A-100 | 5 |
| 4 | A-100 | 10 |
| 4 | D-400 | 12 |
| 4 | E-505 | 8 |
One row in, several rows out. Each event's PAYLOAD held an array of items, and each item became its own row with its own SKU and QTY.
Event 4 carried three items, so it occupies three rows. Event 2 carried one, so it occupies one.
Notice that this is the same mechanism as everything else on this page. FLATTEN needs to see E.PAYLOAD from the row beside it, which is precisely the correlation LATERAL makes legal.
LATERAL FLATTEN is the doorway into semi-structured data, which gets a whole level of its own later in this course. VARIANT, the : path operator, the :: cast, and what FLATTEN's other output columns are for. Here it is just the clearest possible example of a table expression that has to see the outer row.
So when do we use it, and what do we use instead?
Three decisions, and the first one is the only one most people need.
| What you want | Reach for | Why |
|---|---|---|
| Top N per group | QUALIFY with ROW_NUMBER() | The lateral idiom with a LIMIT inside it returned Unsupported subquery type here, both spellings of it |
| One aggregate per outer row | LEFT JOIN and GROUP BY first, LATERAL if that gets awkward | Both are on this page returning identical grids, and the simpler one is easier to read and to change |
| An array expanded into rows | LATERAL FLATTEN | There is no non-lateral way to write it. The table function has to see the row it is expanding |
And one habit, which costs nothing. When a lateral subquery returns rows rather than an aggregate, count your result against the source table before you trust it. The query that quietly drops the empty groups is indistinguishable, by eye, from the query that keeps them.
Key points
- A subquery in the
FROMclause cannot refer to the rows beside it. Try and Snowflake returnsinvalid identifier. LATERALin front of that subquery makes the correlation legal: it is evaluated once per outer row and can see that row.- Order matters. A lateral subquery can only see table expressions listed before it in the
FROMclause. - A comma
LATERALbehaves like an inner join. If the subquery returns no rows for an outer row, that outer row vanishes — silently. - Unless the subquery aggregates. An aggregate with no
GROUP BYalways returns exactly one row, so nothing can be dropped. The subquery decides, not the join. - The PostgreSQL top-N-per-group idiom — a correlated
LATERALcontainingLIMIT— returnedUnsupported subquery type cannot be evaluatedhere, and so didLEFT JOIN LATERAL ... ON TRUE. - For top N per group in Snowflake, use
QUALIFYwithROW_NUMBER(), and keepNULLS LASTin the window'sORDER BY. On this data it is what makes Delta Print Co's top order the largest real amount instead of the unknown one. - When a plain
LEFT JOINandGROUP BYgives the same grid, use it.LATERALis not the simpler form. LATERAL FLATTENis the version you will write most: one row in, one row out per element of a semi-structured array.
In the next post we will cover UNION and UNION ALL. A join puts two tables side by side. A set operator stacks one result on top of another, and one of those two keywords quietly removes a row that was never a duplicate record.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the shared sample-data script for this course. The CUSTOMERS, ORDERS and EVENTS_JSON data is invented demo data created by that script. The three error panes are Snowflake's own messages, captured as returned and not paraphrased; the observation about which queries failed is stated only as far as those captures support it, and is not offered as documented behaviour. No performance claims are made anywhere in this series — nothing here was timed, so nothing about LATERAL being faster or slower than a grouped join is said. Clause behaviour is described from the Snowflake LATERAL documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.