Statement binding/execution order in Snowflake

Question

One of the things I like about Snowflake is it's not as strict about when clauses are made available to other clauses. For example in the following:

WITH tbl (name, age) as (
    SELECT * FROM values ('david',10), ('tom',20)
)
select
    name,
    age,
    year(current_timestamp())-age as birthyear
from
    tbl
where
    birthyear > 2010

I can use birthyear in the WHERE clause. This would be in constract to something like SQL Server, where the binding is much more strict, for example here. Is there any documentation about the binding order in Snowflake, or is generally 'loose' and the parser will figure things out for you?

Or, same as in MySQL5.7:

select
    name,
    age,
    year(now())-age as birthyear
from
    (select 'tom' name, 20 age union select 'david', 10) tbl
where birthyear > 2010

Unknown column 'birthyear' in 'where clause'

I don't know if it's documented somewhere, but barring some exceptions you can use a computed column's alias in any subsequent clause, not only the where clause. You can use it in the GROUP BY, HAVING, a table expression lower in a chained CTE, etc. I've used that last capability to improve performance on certain query patterns. About the only place you can't use it are the places you can't use any column name such as certain subqueries. — Greg Pavlik, Oct 25 '21 at 22:09

score 1 · Answer 1 · answered Oct 25 '21 at 22:31

1

This behaviour has been treated in a community post right here.

When using column aliases, Snowflake considers EXPLICIT names first, then IMPLICIT/ALIASED names second.

Hope this will answer to your question.

answered Oct 25 '21 at 22:31

CMe

642
3
9

Lukasz Szozda · Accepted Answer · 2021-10-26T17:57:01.580

Disclaimer: It is my way of understanding this feature and may be incomplete

The construct like below would fail as birthyear is unknown.

select
    name,
    age,
    year(current_timestamp())-age as birthyear
from  tbl
where birthyear > 2010;

Normally we use subqueries or simply copy the expression:

select *
from ( select
          name,
          age,
          year(current_timestamp())-age as birthyear
      from  tbl) s
where birthyear > 2010;

select
    name,
    age,
    year(current_timestamp())-age as birthyear
from  tbl
where year(current_timestamp())-age > 2010;

There is one more way to achieve it using LATERAL JOIN/CROSS APPLY:

select
    name,
    age,
    s.birthyear
from  tbl
,LATERAL(SELECT year(current_timestamp())-age as birthyear) s
where s.birthyear > 2010;

We could go one step further and alias twice at the same level:

select
    name,
    age,
    year(current_timestamp())-age as birthyear,
    CASE birthyear > 2000 THEN 'XXI'ELSE 'XX' END AS century
from  tbl
where birthyear > 2010;

Which would be equivalent of chained LATERAL JOINS:

select
        name,
        age,
        s.birthyear,
        s2.century
 from  tbl
 ,LATERAL (SELECT year(current_timestamp())-age as birthyear) s
 ,LATERAL (SELECT CASE WHEN s.birthyear > 2000 THEN 'XXI'ELSE 'XX' END AS century) s2
 where s.birthyear > 2010;

db<>fiddle demo

Snowflake's version is kind of "inlined version of LATERAL JOIN" which is pretty neat.

Related: PostgreSQL: using a calculated column in the same query

Worth mentioning: this syntax will not allow to nest windowed function Using nested window function in Snowflake or use them in WHERE:

SELECT *, ROW_NUMBER() OVER(ORDER BY col1) AS rn
FROM t
WHERE rn > 10;

=>
SELECT *, ROW_NUMBER() OVER(ORDER BY col1) AS rn
FROM t
QUALIFY rn > 10;

@lukraz -- awesome answer, thank you. Could you please explain how `We could go one step further and alias twice at the same level:` works? — David542, Oct 26 '21 at 15:29
@David542 It means you could refer to aliases at the same level, `SELECT col + 1 AS col2, col2 + 2 AS col3, col3 * col 3 AS col4` .This feature is called [lateral column alias reference](https://aws.amazon.com/about-aws/whats-new/2018/08/amazon-redshift-announces-support-for-lateral-column-alias-reference/) or [SAS CALCULATED](https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002294533.htm) — Lukasz Szozda, Oct 26 '21 at 15:34
oh, very cool. Is that supported in most dbs? postgres, mysql, sqlserver? — David542, Oct 26 '21 at 15:36
@David542 Not really, few dialects support such syntax(none of the one you mentioned) and it has its own quirks — Lukasz Szozda, Oct 26 '21 at 15:37

Statement binding/execution order in Snowflake

2 Answers2