Redshift Lateral Column Alias – Derived Column

  • Post author:
  • Post last modified:September 24, 2024
  • Post category:Redshift
  • Reading time:8 mins read

Redshift lateral column alias sometimes called derived columns are columns that are derived from the previously computed columns in same SELECT statement. Derived columns or computed columns are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. Many PostgreSQL relational databases such as Netezza supports lateral column alias. Recently Redshift also started supporting lateral column alias reference. In this post, lets us explore that new feature.

Redshift Lateral Column Alias

Page Content

Introduction

Amazon Redshift has introduced a much needed lateral column alias reference feature. This feature enhancement allows you to reference a column alias within the same query immediately after it is declared. This can significantly improve the readability and efficiency of your SQL queries.

Why Use Lateral Column Alias in Redshift?

The support for lateral column alias reference in Amzon Redshift enables you to write queries without repeating the same expressions in the SELECT list. For example, you can define the alias ‘probability’ and use it within the same select statement:

select clicks / impressions as probability, 
round(100 * probability, 1) as percentage 
from some_data;

Here are some of the benefits of lateral column alias in Amazon Redshift:

  1. Enhanced Readability: By using lateral column aliases, you can make your queries more readable and easier to understand. This is particularly useful in complex queries where the same expression might be used multiple times.
  2. Increased Efficiency: It reduces the need to repeat expressions, which can simplify query writing and maintenance. This optimizes the query performance in Redshift.

How Lateral Column Alias works in Redshift? Example

Lateral column aliasing allows you to define an alias and then use it within the same SELECT statement.

Here’s a simple example to illustrate this:

SELECT 
    clicks / impressions AS probability, 
    ROUND(100 * probability, 1) AS percentage 
FROM 
    some_data;

In this example, the alias probability is defined and then immediately used in the calculation of percentage within the same SELECT statement. This not only makes the query more readable but also reduces redundancy.

Alternative Methods to Lateral Column Alias in Redshift

The lateral column alias or derived columns are very common in relational databases. You will find them extensively used in complex queries.

There are two methods that you can use as an alternative methods to lateral column alias in SQL.

Compute Column Using Redshift WITH clause

This is one of the easiest method that you can use to derive columns.

For example, consider below query that compute derive column.

WITH cte1 
     AS (SELECT col1, 
                col1 * 10 AS der_col2 
         FROM   sample_table1) 
SELECT a.col1, 
       b.der_col2, 
       b.der_col2 * 100 AS der_col3
FROM   sample_table1 a 
       JOIN cte1 b 
         ON( a.col1 = b.col1 );

 col1 | der_col2 | der_col3
------+----------+----------
    1 |       10 |     1000
   10 |      100 |    10000
   20 |      200 |    20000
(3 rows)

As you can see, der_col2 is derived from the col1 and der_col3 is derived by reusing der_col2 .

You cam read more about Redshift WITH clause in my other post:

Subquery to Derive Column

Another simple method is to use subquery to derive column and reuse that column in upper SELECT statement.

For example, consider below query with subquery.

SELECT sub.col1, 
       sub.der_col2, 
       sub.der_col2 * 100 AS der_col3
FROM
(
SELECT col1, 
                col1 * 10 AS der_col2 
         FROM   sample_table1
) as sub;

 col1 | der_col2 | der_col3
------+----------+----------
    1 |       10 |     1000
   10 |      100 |    10000
   20 |      200 |    20000
(3 rows)

As you can see, der_col2 is derived from the col1 in subquery and der_col3 is derived by reusing der_col2 in upper SELECT clause.

You read more about subqueries in my other post:

Conclusion

The new lateral column aliasing feature in Amazon Redshift makes SQL queries more efficient and easier to handle. It helps you write cleaner and more maintainable code, saving time and reducing mistakes. If you can’t use lateral column aliasing, you can still use CTEs (Common Table Expressions) and subqueries to achieve similar results.

Hope this helps 🙂

Tags: ,