Permitted Sub-queries in Snowflake

Question

in the Snowflake documentation it lists the types of sub-query that are supported Types Supported by Snowflake:

Uncorrelated scalar subqueries in any place that a value expression can be used.
Correlated scalar subqueries in WHERE clauses.
EXISTS, ANY / ALL, and IN subqueries in WHERE clauses. These subqueries can be correlated or uncorrelated.

However, in the documentation about windows functions (as an example) it gives this SQL which runs without error:

select branch_id,
       net_profit as store_profit,
       (select sum(net_profit) from store_sales as s2 where s2.city = s1.city) as city_profit,
       store_profit / city_profit * 100 as store_percentage_of_city_profit
    from store_sales as s1
    order by branch_id;

From my understanding:

city_profit is defined by a sub-query and that sub-query is correlated (it is referencing S1)
It is not covered by any of the 3 rules regarding allowable sub-queries (it is not uncorrelated, it is not in a WHERE clause and it is not "EXISTS, ANY / ALL, and IN subqueries in WHERE clauses"

Therefore the query should be invalid but as it isn't I am obviously missing something. What have I misunderstood?

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2020-11-03T22:28:09

Correlated subqueries are a complex topic for any SQL database. With that said, you shouldn't be surprised to find out that Snowflake keeps improving its support for correlated subqueries.

Looking at these docs, they never say that a correlated subquery at the SELECT level is forbidden - and Snowflake can in fact run the query in the example.

If you ever find a query that can't run with a correlated subquery, please bring a reproducible example to Stack Overflow — and we'll work together to find a solution

Permitted Sub-queries in Snowflake

1 Answers