Amazon Redshift Derived Tables and Examples

  • Post author:
  • Post last modified:July 21, 2021
  • Post category:Redshift
  • Reading time:4 mins read

When you work in an enterprise data warehouse (EDW), you will work with multiple data sources. Sometimes you may have to derive the table to get only required columns instead of joining big table. This process not only improve the performance, but also avoid unnecessary memory usage. In this article, we will check how to improve the performance of Redshift query by creating derived tables with some examples.

Amazon Redshift Derived Tables

In the real world scenario, you may derive the some column from base table and instead of creating temporary table you may use that derived query in FROM clause of the SQL statement or create with clause statement. These types of queries are called derived tables.

Types of Derived Tables

Mainly, there are two type of derived tables used in SQL:

  • Derived Query in FROM clause.
  • Create WITH clause.

Now, let use verify these two types in detail.

Derived Query in FROM Clause

A derived table is basically a subquery which is always in the FROM clause of a SQL query Statements. The reason it is called a derived table is because it essentially functions as a table as far as the entire query is concerned.

These derived tables are built dynamically using additional SELECT within the query. The derived tables are stored on the leader node memory and discarded as soon as the query execution is completed.

For example, consider below derived table example in the FROM clause.

SELECT Max(age) 
FROM   ( 
       --this part of the query is a derived table  
       SELECT age 
        FROM   person_table) AS Age -- derived table have an alias name.

Some relational database require you to provide alias name to the derived table. But, it is good practice to provide alias.

Redshift WITH Clause

The second type of derived table is Redshift WITH clause. This type of derived tables are created once and use multiple times. The derived table created using WITH clause can be accessed at multiple locations in the same SQL query statement.

For example. consider below sample with clause example.

WITH patient_cte AS 
( 
       SELECT * 
       FROM   patient 
       WHERE  id = 1001) 
SELECT   * 
FROM     patient_cte 
ORDER BY 1 limit 10;

You can read more about Redshift WITH clause and its restrictions in my other post: Amazon Redshift WITH Clause Syntax, Usage and Examples.

Related Articles,

Hope this helps 🙂

Tags: ,