Amazon Redshift - Lateral Column Alias Reference

Amazon Redshift - lateral column alias reference

This syntax is not safe. In fact, merely inlining the code means that it does not even provide a performance advantage. It is only syntactic sugar.

Given that there are easy alternatives -- CTEs and subqueries -- I would just avoid this new "feature".

If there were a setting to turn this off, I would recommend using it.

Incidentally, many newcomers to SQL find this quite disconcerting. This purpose is to avoid ambiguity. What should the following query return?

select (a + 1) as b, b 
from (select 1 as a, 0 as b) x;

The designers of SQL probably felt that the rules around resolving such situations are more complex than merely rewriting a subquery.

The one "database" that I know of that resolves this well is actually SAS proc SQL. It introduced the calculated keyword, so you can write:

select (a + 1) as b, calculated b, b
from (select 1 as a, 0 as b) x;

And this would return 2, 2, 0.

In other words, I don't think Amazon put much thought into the implementation of this "feature".

I assume that the problem is coming from the SELECT in

THEN (SELECT split_part(contact_email, '@', 2))

Could you remove the SELECT? I don't think it's required.