Greenplum WITH Clause Syntax, Usage and Examples

Post author:Vithal S
Post last modified:May 10, 2019
Post category:Greenplum
Reading time:5 mins read

The WITH clause in SQL is sometimes referred to as a common table expression (CTE), is an optional clause that will always precedes SELECT statement of the query block. WITH clause in Greenplum contains a sub-query that is defined as a temporary table like view. Each sub-query in the WITH clause is associated with a table name, an optional list of column names that CTE returns, and a query expression that evaluates to a table (usually a SELECT statement). In this article, we will check how to use Greenplum WITH clause, its syntax, usage and some examples.

Where you can use Greenplum WITH Clause?

WITH clause in the SQL are usually used to encapsulate repetitive and complex code. You can refer WITH clause as a normal table within query context. You can also use the WITH clause to improve the speed for Greenplum complex sub-queries and improve overall Greenplum performance.

The main advantage of WITH clause is, you can use it wherever SELECT clause is acceptable.

You can use WITH clause in,

INSERT INTO … SELECT
UPDATE
CREATE VIEW
DELETE
CTAS
SELECT

The RECURSIVE CTE in Greenplum is in experimental mode. It is advised not use them in production environments.

Greenplum WITH Clause Syntax

Below is the syntax for WITH clause:

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

Greenplum WITH Clause Examples

Select ID of the name ‘green’:

WITH cte1 
     AS (SELECT id 
         FROM   export_test 
         WHERE  NAME = 'green') 
SELECT * 
FROM   cte1;

Greenplum WITH Clause in an INSERT Statement

You can use WITH clause in INSERT below SELECT statement.

For example:

INSERT INTO export_test 
WITH cte AS 
( 
       SELECT 1       AS id, 
              'green' AS NAME, 
              'BEN'   AS city 
)SELECT * 
FROM   cte;

WITH Clause in an UPDATE Statement

You can use WITH clause in UPDATE statement WHERE clause subquery.

For example:

WITH clause in DELETE statement

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

For example:

DELETE FROM export_test 
WHERE  id = (WITH cte1 
                  AS (SELECT id 
                      FROM   export_test 
                      WHERE  name = 'green') 
             SELECT * 
              FROM   cte1);

WITH clause in CREATE TABLE AS Statement

WITH clause in CREATE TABLE AS statement:

CREATE TABLE tb1 AS WITH cte1 AS
  (SELECT id
   FROM export_test
   WHERE name = 'green')
SELECT *
FROM cte1;

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

Tags: Greenplum