I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.
In this post I’ll talk about how you can easily re-use aliased expressions in Snowflake. For example, you can do something like this (using the StackOverflow sample database):
That’s right. I defined an expression – a concatenation of two fields – and used the alias of that expression in another expression. Furthermore, I used the alias of the second expression in the WHERE clause. Gasp.
In Snowflake, you can re-use your aliases in other expressions and in the WHERE, GROUP BY and HAVING clause. Awesome. In SQL Server, you will be greeted with the following error:
Boo. I submitted an idea on the feedback site. You can still vote for it. It has over 100 votes and it is under review. The main criticism is: but what if the alias is the same as one of the columns of the original table? Let’s test this out:
It seems the original column name has precedence over any alias defined. Unfortunately, it’s not all cookies and rainbows. You can’t use this technique with window functions. Suppose we have the following sample data:
If we want to filter out duplicate rows by keeping the oldest record (thus removing the record for Bob with ID = 4), we can use the ROW_NUMBER function to achieve this. In SQL Server, the query would look like this:
You might be tempted to use the aliasing technique in Snowflake to shorten your code. However, this errors out:
Basically, when you use the aliasing technique, the Snowflake compiler will just copy paste the aliased expression wherever referenced. But window functions can only appear in SELECT or ORDER BY clauses, so they will error out if used anywhere else. It seems we will have to keep using the common table expressions for readable code.
In conclusion, you can re-use aliased expressions directly in other clauses, which is really useful if you have a lot of expressions that build upon other expressions. It avoids the use of subqueries. But you can’t use it for window functions.
Other parts in this series: