Amazon Redshift WITH Clause Syntax, Usage and Examples

Redshift WITH Clause is an optional clause that always precedes SELECT clause in the query statements. WITH clause has a subquery that is defined as a temporary tables similar to View definition. Each subquery in the WITH clause specifies a table name, an optional list of column names, and a query expression that evaluates to a table (usually a SELECT statement).

In SQL, WITH clause are commonly referred to as Common Table Expressions (CTE). A CTE or WITH clause is a syntactical sugar for a subquery.

Where you can use Redshift WITH Clause?

If your requirement is to reuse the piece of query result in same query construct, then you can use the WITH clause. You can also use the WITH clause to improve the speed for Redshift complex sub-queries and improve overall Redshift performance.

Read:

The main advantage of WITH clause is, you can use it wherever SELECT clause is acceptable in the SQL script or query. For instance, you can use it in:

INSERT INTO … SELECT
UPDATE – Within a WHERE clause of subquery
CREATE VIEW
DELETE
CTAS
SELECT

Similar to the other databases like Teradata, Amazon Redshift also supports creating RECURSIVE using WITH clause.

Redshift WITH Clause Syntax

Below is the syntax for WITH clause:

[ <WITH [RECURSIVE] clause> ] < <with list element> [ { <comma> <with list element> }... ]> 
<query expression body>;

Redshift CTE Examples

Select only patients with ID 1001:

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

Above query is 100% equivalent to following query.

SELECT *
FROM   (SELECT *
        FROM   patient
        WHERE  id = 1001) AS patient_CTE
ORDER  BY 1
LIMIT  10;

Redshift CTE in an INSERT Statement Example

You can use WITH clause in INSERT below SELECT statement. For example:

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

Redshift CTE in an UPDATE Statement Example

You can use WITH clause in UPDATE statement WHERE clause subquery. For example:

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

Redshift WITH clause in DELETE statement

You can use the WITH clause in DELETE statement WHERE clause subquery. For example:

DELETE
FROM   test_tbl
WHERE  id IN ( WITH sample_rec AS
              (
                     SELECT *
                     FROM   table
                     WHERE  id IS NULL )SELECT *
       FROM   sample_rec );

Redshift WITH clause in CREATE TABLE AS Statement

WITH clause in CREATE TABLE AS statement:

create table TestCTEtable 
as
WITH CTE AS
( 
 SELECT current_timestamp as col1
)
SELECT col1 
from CTE;

Redshift Recursive CTE

Amazon Redshift, a fully-managed cloud data warehouse, now adds support for Recursive Common Table Expression (CTE) to analyze hierarchical data, such as organizational charts where employees reports to other employees (managers), or multi-level product orders where a product consists of many components, which in turn consist of other components.

For example, you can use the following recursive query for hierarchical data.

with recursive org_cte(id, name, manager_id, level) as
( select id, name, manager_id, 1 as level
  from employee
  where name = 'John'
  union all
  select e.id, e.name, e.manager_id, level + 1
  from employee e, john_org j
  where e.manager_id = j.id and level < 4 
  )
 select id, name, manager_id from org_cte order by manager_id;

Redshift WITH Clause Restrictions

Below are some of WITH clause restrictions:

You cannot specify another WITH clause inside a WITH clause subquery.
You cannot make forward references to tables defined by WITH clause subqueries