Analytical SQL Tips Series — Lateral Column Alias

What’s the problem with the following SQL statement?

SELECT 42 AS a, a + 1 AS b

The answer is quite simple: “ambiguity”.

In Postgres, you’ll actually get an error that “column ‘a’ does not exist.” You’d have to write Common Table Expressions (CTEs) or subqueries to solve a problem like this.

Have you ever found yourself puzzled, especially in the early days of learning SQL, about why it’s not possible to reuse an expression you’ve already defined in the same SELECT list 🧐 ?

This is where the Lateral Column Alias comes into play. The term “lateral” may be a bit confusing, but it simply refers to reusing what is already in the SELECT list (think of it as referring to the left side of the query). With the Lateral Column Alias, you can easily reuse what you have already defined in the SELECT list.

Wait, there are still some unresolved problems. So, what might the “Ambiguity” problem entail? Consider the following query:

 SELECT 42 AS a, a + 1 AS b FROM t

What if the table t already has a column called “a” ?
Should “b” be 42 + 1, or should it be the column “a” + 1 ?

That’s where the ambiguity arises. Generally, the column name will have higher priority, but it really depends on your data warehouse. So, use the Lateral Column Alias carefully.

With Lateral Column Alias, you can reuse it in any expression in the SELECT list item. This is a very handy feature. DuckDB, Snowflake, Databricks, and AWS Redshift all support it.

Try it on DuckDB WASM . It takes about 10 seconds to initialize DuckDB and create the table

CREATE TABLE Weather AS 
FROM 'https://recce-dbt-tips.s3.amazonaws.com/central-park-weather.csv';

SELECT 
    SUM(CASE WHEN PRCP > 0 THEN 1 ELSE 0 END) / COUNT(*) AS probability_of_precipitation,
    ROUND(100 * (SUM(CASE WHEN PRCP > 0 THEN 1 ELSE 0 END) / COUNT(*)), 1) AS percentage_of_precipitation
FROM Weather;

SELECT 
    SUM(CASE WHEN PRCP > 0 THEN 1 ELSE 0 END) / COUNT(*) AS precipitation_ratio,
    ROUND(100 * precipitation_ratio, 1) AS percentage_of_precipitation
FROM Weather;

You can apply these SQL tip in your dbt code review. If you want to know more about analytical SQL tips, follow Recce on the Linkedin.