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
Related Articles,
Hope this helps 🙂