Redshift WITH Clause: Syntax, Usage & Example Queries Simplified 101

Many companies worldwide use vast amounts of data to make data-driven decisions that help excel their business growth. However, a colossal amount of user data can bring several complexities while analyzing data. Consequently, to manage vast amounts of data effectively, companies use Amazon Redshift, a Data Warehouse, for organizing and processing Big Data for better decision-making. 

To work with Amazon Redshift, you can use SQL Queries for retrieving, altering, and updating data. SQL has several clauses that help write simple and effective queries while executing data-related tasks. One of those is the WITH Clause, which helps create a temporary subquery table right before the main SQL Query. This makes the long subqueries separate, allowing you to write simple queries.

In this article, you will learn about Amazon Redshift, how to use Amazon Redshift for performing SQL operations, and different types of SQL commands. You will also learn how to use the Amazon Redshift WITH Clause in SQL statements, examples, and usage notes of SQL Statements. 

Table of Contents

• What is Amazon Redshift?
• What is Amazon Redshift Query Editor?
• What is SQL?
• Key Types of SQL Commands
• How to use the Redshift WITH Clause? 
  • Syntax of Redshift WITH Clause
  • Parameters of Redshift WITH Clause
  • Usage Methods of Redshift WITH Clause
  • Example Queries of Redshift WITH Clause
• Conclusion

What is Amazon Redshift?

Amazon Redshift is a fast and scalable Data Warehouse, which not only allows developers to analyze the data present in the Warehouse but also the data present across custom Data Warehouses and Data Lakes. Amazon Redshift delivers 10x Faster Performance in Analytics when compared to other Data Warehousing Services. It achieves this because of the High Scalability attained through Parallel Query Execution and Columnar Storage on high-performing disks.

Amazon Redshift also enables Fast Query Performance, provides high scalability to work across different users, and improves I/O Efficiency by scaling the data up and down according to the performance and capacity. Ever since its launch in 2013, Amazon Redshift has been one of the fastest-growing services among AWS services, having thousands of customers across organizations worldwide. Using Amazon Redshift, developers can set up and deploy a new Data Warehouse in minutes and effectively run queries across petabytes of data.

Since Amazon Redshift is a Fully-managed Data Warehouse, it allows users to automate administrative tasks to only focus on optimizing the data to make data-driven business decisions instead of doing repetitive tasks. Its design relies on a robust communication connection between the Client Application and the Data Warehouse Cluster.

You can check the Amazon Redshift Manual for additional information about Amazon Redshift Infrastructure.

What is Amazon Redshift Query Editor?

Amazon Redshift Query Editor is the web-based query authoring tool used for data querying purposes. Using Amazon Redshift Query Editor, developers can explore, edit, process, & analyze data with SQL Queries. It does not require any driver or software installed in your local machine. Developers can directly execute queries in the Amazon Redshift Query Editor after creating a Cluster in the Amazon Redshift console.

The query editor provides users with the built-in version control capability, which allows them to see how a query has changed over time. This enables users to revert to the previous versions and save any earlier versions as a new query or file.  

To work with the Query Editor, you can log in to the web-based interface via the Direct URL. Once logged in, you can see various Clusters you have already created and worked on using the Amazon Redshift console. You just need to select the cluster you want to work with and start querying. If you create and connect a cluster for the first time, you must provide the Database credentials such as Database Name, User Name, and Authentication Methods.

What is SQL?

Created in the 1970s by IBM Researchers Raymond Boyce and Donald Chamberlin, SQL (Structured Query Language) is a query language to perform operations like creation, deletion, manipulation in Relational Databases. It is a front-end query language in RDBMS (Relational Database Management Systems) like MySQL, Oracle, and PostgreSQL to query data in the databases.

SQL is a simple and easy-to-learn language since the query statements consist of descriptive English words and case-insensitive keywords. Users can write queries using SQL for performing data operations like defining, updating, and manipulating data in a database. This process includes altering rows and columns, declaring constraints to the data, deleting and dropping tables in a database, and much more.

SQL can work on various systems or platforms and can also be embedded within other languages using SQL libraries, modules, and Pre-compilers. For instance, by importing and calling the sqlite3 library in Python code, users can perform queries within the Python Framework itself. Such flexibilities make SQL, a popular language among all Data Professionals to efficiently work with a colossal amount of data.  

Key Types of SQL Commands

SQL Query Statements usually consist of the commands/keywords under four categories. They are Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL) and Transaction Control Language (TCL).

• DDL: It includes commands for restructuring the existing Relational Schemas of a database like creating, deleting, and modifying tables. The DDL commands are auto committed, i.e., it saves all the changes permanently in the database.
  • CREATE: To create a new table/database.
  • RENAME: To rename a table in a database.
  • ALTER: To alter/change the table.
  • TRUNCATE: To delete data from the table.
  • DROP: To drop the table.

• DML: This includes Data Manipulation commands to manipulate data present in the database. The DML commands are not auto committed, i.e., the changes are not permanent to the database and can be rolled back anytime.
  • SELECT: To retrieve desired data from a database.
  • UPDATE: To update the data present in a table.
  • INSERT: To insert data into a table.
  • DELETE: To delete records present in a table.

• DCL: This includes the commands that control a users’ access levels on the database or tables. In other words, these commands provide security to the database objects by granting rights, permissions, and privileges to the users.
  • GRANT: To grant users the access privilege to databases.
  • REVOKE: To withdraw the user access privilege, which was previously given by the GRANT command.

• TCL: The commands under TCL are used to manage transactions in the database. In addition, it controls and manages transactions (a unit of work performed in a database) made by DML commands. Usually, transactions are temporary in the database whenever any query operation is executed. TCL commands are used to make the changes permanently in the database. These commands can only be used with DML commands since DDL commands are auto committed.
  • COMMIT: It is used to permanently save the transactions made in the database. 
  • ROLLBACK: It rolls back or undoes a transaction when an error has occurred.
  • SAVEPOINT: It creates checkpoints to temporarily save the data at any point of a transaction so that it can be rolled back whenever required.

How to use the Redshift WITH Clause?

The WITH Clause is an optional clause always written with the main SQL Query to make it easy for a user to break down the complex queries. It is also referred to as CTE (Common Table Expressions) and Subquery Factoring.

The Redshift WITH Clause can be used in a query statement to create a temporary result set so that it can be used multiple times with the select, update, insert or delete commands. In other words, the Redshift WITH Clause creates a temporary virtual table that can be further utilized in the main SQL Queries. 

For example, consider the above image. Here, the WITH clause creates a temporary table and assigns it as “Sales_CTE.” Now, the result of the first query statement written within the WITH Clause is stored in Sales_CTE. Following that, you can see the second query statement that references the previously created Sales_CTE. With this method, you can reference or call the Subquery Block multiple times in any query you write further by specifying the temporary table name.

• Syntax of Redshift WITH Clause
• Parameters of Redshift WITH Clause
• Usage Methods of Redshift WITH Clause
• Example Queries of Redshift WITH Clause

1) Syntax of Redshift WITH clause

[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]

The above statement is the Basic Syntax of the WITH Clause, where common table expression can be either recursive or non-recursive. 

Recursive CTE: When the newly created CTE or temporary table references itself within that CTE, it is called a Recursive CTE. It is used when working with hierarchical or structured data since CTE runs recursively until the query returns the complete hierarchy. To terminate the recursion, a WHERE Conditional Statement should be provided in the query. 

Syntax for Recursive CTE

CTE_table_name (column_name [, ...] ) AS ( recursive_query )

Non-Recursive CTE: Non-Recursive CTE does not reference itself inside the newly created CTE. Non-Recursive CTEs are simpler than recursive CTEs because Non-Recursive CTEs do not follow any repeated processing, recursion on subroutine. 

Syntax for Non-Recursive CTE

CTE_table_name [ ( column_name [, ...] ) ] AS ( query )

2) Parameters of Redshift WITH Clause

• common_table_expression: This parameter refers to the temporary table you create using the WITH Clause, which can be further referenced in the queries you write later. 
• RECURSIVE: It is used when the common table expression contains a Recursive Query in the WITH clause. This keyword should be used right after the WITH keyword, even when the WITH clause has multiple Recursive CTEs.
• CTE_table_name: It is the unique name given to the result of a Subquery written within the WITH Clause. Using this unique name, users can reference the result set of the Subquery by calling it in further queries.
• column_name: This is the list of columns created in the temporary table after executing subqueries written within the WITH Clause. For a Non-recursive CTE, the column_name parameter is optional, but for a Recursive CTE, the column_name parameter list is required.
• query: This represents any SELECT Query statement that supports Amazon Redshift.

3) Usage Methods of Redshift WITH Clause

The WITH Clause can be used wherever the SELECT Clause is used in the SQL Script or Query. It can also be used with the SQL statements that contain,

• SELECT INTO
• CREATE TABLE AS
• CREATE VIEW
• DECLARE
• EXPLAIN
• INSERT INTO
• PREPARE
• UPDATE
• DELETE

4) Example Queries of WITH Clause

The Redshift WITH clause can be used in different ways by combining or sub-querying with the main SQL query statements. Some of the examples are:

• WITH Clause in a SELECT Statement. 
• WITH Clause in an INSERT Statement.
• WITH Clause in an UPDATE Statement. 
• WITH Clause in a CREATE TABLE AS Statement.

A) WITH Clause in a SELECT Statement 

with venuecopy as (select * from venue)
select * from venuecopy order by 1 limit 10;

In the above example, the Amazon Redshift WITH Clause is used with the SELECT statement, where “venuecopy” is the name of the temporary table created using the WITH Clause. Following that, you can also find “venuecopy” referenced in the main SQL Statement.

B) WITH Clause in an INSERT Statement

INSERT INTO table1 WITH CTE AS(SELECT col1 as col1)
SELECT col1 from CTE;

The Amazon Redshift WITH Clause can also be used in between the two SQL Statements. The above one is a suitable example for understanding this different way of using the WITH Clause. The WITH Clause is used between the first SQL Query INSERT INTO table1 and the second SQL query SELECT col1 from CTE.

C) WITH Clause in an UPDATE Statement 

UPDATE test_tbl
SET sample_id = 100
WHERE  id = ( WITH sample_rec AS(SELECT * FROM table)
SELECT *FROM   sample_rec WHERE  id IS NULL);

In the above example, the Amazon Redshift WITH Clause is used inside the WHERE Clause. The WHERE Clause is one of the conditional statements in SQL, which executes queries only when the condition is satisfied. The WITH Clause can also be used between the two main queries containing more than one conditional statement.  

D) WITH Clause in a CREATE TABLE AS Statement 

CREATE TABLE TestCTEtable AS
WITH CTE AS (SELECT current_timestamp as col1)
SELECT col1 from CTE;

In SQL, CREATE TABLE AS statement is used to create a new table and stores the value or result sets of the other temporary table. In the above-given example, the CREATE TABLE AS command creates a new table in the name of TestCTEtable. Following that, you can find the WITH Clause, which creates a temporary table in the name of CTE and performs the query operation written inside it. Now, a result set of the Amazon Redshift WITH Clause operation is stored in the table that was created before. 

Conclusion

Amazon Redshift serves as a one-stop platform for managing, optimizing, and analyzing over petabytes of data. It eases the work of developers by providing a single framework that does several data operations.

In this article, you have learned about Amazon Redshift WITH Clause, how to write SQL Queries in Amazon Redshift and the different ways of writing the WITH Clause in SQL Statements. However, SQL has many clauses like AND, OR, LIMIT, GROUP BY, ORDER BY, which you can explore later. 

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo Data saves the day! Hevo Data offers a faster way to move data from 100+ Sources such as Databases or SaaS applications into your Data Warehouse such as Amazon Redshift to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin? SIGN UP for a 14-day Free Trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about the Amazon Redshift WITH Clause in the comments section below!