How use Lateral Column Alias in Redshift?

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

Redshift lateral column alias, also known as derived column, is created from previously computed columns in the same SELECT statement. These columns are virtual and not stored in the table; their values are recalculated each time they are used in a query. Many PostgreSQL databases, like Netezza, support lateral column aliases. Recently, Redshift has also started supporting this feature. In this post, let’s explore this new capability.

Lateral Column Alias in Redshift

Page Content

Introduction

Amazon Redshift now has a new feature called Lateral Column Alias. This feature lets you use a column alias in the same query right after you create it. This can make your SQL queries easier to read and faster to run, helping with Redshift Performance Optimization and SQL Performance Tuning.

Benefits of Using Lateral Column Alias in Redshift

With Lateral Column Alias support in Amazon Redshift, you can write queries without having to repeat the same expressions in the SELECT list. For instance, you can create a column alias like ‘conversion_rate’ and use it right away in the same SELECT statement.

select sales / visits as conversion_rate,
round(100 * conversion_rate, 2) as conversion_percentage
from sales_data;

Here are some benefits of using Lateral Column Alias in Amazon Redshift:

  • Enhanced Readability: Using lateral column aliases makes your queries easier to read and understand, especially in complex queries where the same expression is used multiple times. This helps with SQL Performance Tuning.
  • Increased Efficiency: It reduces the need to repeat expressions, simplifying query writing and maintenance. This improves Redshift Performance Optimization.

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:

select sales / visits as conversion_rate,
round(100 * conversion_rate, 2) as conversion_percentage
from sales_data;

In this example, the alias conversion_rate is created and then used right away to calculate conversion_percentage in the same SELECT statement. This makes the query easier to read and minimizes the code repetition, helping with Performance Optimization and Performance Tuning.

Lateral Column Alias Alternate Methods

The Lateral Column Alias or derived columns are very common in relational databases and are often used in writing complex queries.

Here are two alternative methods you can use instead of lateral column alias in SQL:

  • Use the Redshift WITH Clause or Common Table Expressions (CTE)
  • Use a Subquery in Redshift to derive the column

Derive a Column using Common Table Expressions (CTE)

This one is a easiest method that you can use to derive columns and use the alias in main SELECT statement.

For example, consider below query that compute derive column.

WITH cte1 AS (
    SELECT new_col1, 
           new_col1 * 10 AS new_der_col2 
    FROM new_table1
) 
SELECT a.new_col1, 
       b.new_der_col2, 
       b.new_der_col2 * 100 AS new_der_col3
FROM new_table1 a 
JOIN cte1 b 
ON a.new_col1 = b.new_col1;


 new_col1 | new_der_col2 | new_der_col3
----------+--------------+----------
	1 |           10 |     1000
       10 |          100 |    10000
       20 |          200 |    20000
(3 rows)

As you can see, new_der_col2 is derived from the new_col1 and new_der_col3 is derived by reusing new_der_col2 .

Derive a Column using Redshift Subquery

Another simple method is to use Redshift 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.

Conclusion

Amazon Redshift’s lateral column aliasing feature makes SQL queries faster and easier to manage. It helps you write cleaner code, saving time and reducing errors. If you can’t use lateral column aliasing, you can still use Common Table Expressions (CTEs) and subqueries to get similar results.

Related Articles,

Tags: , , ,