Aliasing

I’ll be starting a series of posts about some cool Snowflake (The cloud data platform) features, that makes SnowSQL really cool – compared to other SQL dialect (I am most familiar with Microsoft SQL Server or T-SQL).

I’ll start the series, talking about aliasing.

Sometimes our SQL get really complex: who haven’t seen a nested case statement grows exponentially? so much that after a while you can’t understand anymore if the output is what is supposed to be, and same for window functions.

The suggestion when complexity arise is usually “break down the problem in smaller steps”, and that’s where aliasing can help the most writing your SQL queries.

Snowflake features of aliasing allows you to re-use the same SQL block in another column definition and simplify massively your code, making it more understandable.

For example you might have a SQL like this:

select 
  case 
    when price > 100 
      and referral_code  = 'XXX1' then price * 0.9
    when referral_code = 'XX2' then price*0.75
    when referral_code = 'XX3' then price - 10 
    else price end as discounted_price,
  case 
    when partner = 'A' then discounted_price*0.2
    when partner = 'B' then discounted_price*0.3
    else discounted_price end as partner_fee
from my_table

So this is the new alternative, without the aliasing capability (for example in a database like SQL Server, you would have to explicitly re-write the logic for the discounted_price twice (not DRY, Don’t Repeat Yourself) or create a function in the database (like this). So much neater using the alias!

Moreover you can use those aliases in the where, group by, having, qualify and order by statement, saving you from copying the same logic multiple times in your query.

Here is another example:

select 
  first_name,
  last_name,
  first_name || ' ' || last_name as full_name
from names
where full_name = 'My Full Name'

In general this feature is amazing, but there are a couple cases where it might trick you. In particular: alias don’t override existing columns names and can have unexpected behaviour when used in join clauses.

Alias conflicting with existing columns names

Snowflake doesn’t infer the alias if you create an alias that is already a columns in one of the tables you join. Suppose the tables look like these:

first table t1

id	email_address
12	foo@com
13	hola@com

second table t2:

my_id	email_address
12	food@com

The following query

select 
  t1.id
  , coalesce( t2.email_address, t1.email_address) as email_address
from t1
left join t2
  on t1.id = t2.my_id
where email_address = 'foo@com'

will give you an error of ambiguous column name, so you’ll need to specify the table for the column you want to use or rename the column alias to be something different from the column names already existing in your tables.

Alias used in joins.

Snowflake doesn’t like aliases in joins, suppose we have two tables above, and say you want to rename the my_id of the second table to new_id :

select 
  t1.id
  ,t2.my_id as new_id
from t1
left join t2 
  on t1.id = t2.new_id

Will give you an error of unknown column names, so careful when using aliases in the join.

To summarise, aliases are amazing to simplify your code and stay DRY but needs to be used with care when joining tables.

Hope you too loves aliases, or start using them after reading this post! Did you find any other troubles using them? Let me know.