Vertica WITH Clause, Syntax, Usage and Examples

  • Post author:
  • Post last modified:May 28, 2019
  • Post category:Vertica
  • Reading time:5 mins read

Let us say you have a requirement to use the results of a piece of code in your complex query. Instead of writing that piece of code everywhere, you can create a temporary table of use Vertica WITH clause. This WITH clause can be used to improve your complex SQL queries that have complex sub-queries, thus improving overall execution speed of your queries.

SQL WITH clause will allow you to name your complex, repeat sub-query. You can use that name anywhere in your query just like normal database table.

Vertica WITH Clause

WITH Clause is an optional clause that always precedes SELECT clause in the query statements. WITH clauses are individually-evaluated SELECT statements for use in a larger container query. You can use WITH clauses to simplify complicated queries and reduce statement or logic repetition.

In SQL, WITH clause does improves the performance by removing overhead of creating and populating temporary tables.

Related articles:

Vertica WITH Clause Syntax

WITH clause syntax in Vertica is similar to other relational databases such as Teradata, Netezza, etc. Below is the WITH clause syntax:

WITH... with-query-1 [(col-name[,…])]AS (SELECT…),
    with-query-2 [(col-name[,…])]AS (SELECT… [with-query-1]),
.
.
    with-query-n [(col-name[,…])]AS (SELECT… [with-query-1, with-query-2, with-query-n[,…]])
<query expression body>;

Vertica WITH Clause Syntax Example

Let’s consider the example of hospital and patients data. You want only subset of hospitals and patient’s data, you can use WITH clause same as below.

WITH hospital (hosp_id, hosp_name, hosp_dept, hosp_type) AS
  (SELECT hosp_id,
          hosp_name,
          hosp_dept,
          hosp_type
   FROM hospitals
   WHERE hosp_type = ‘MSH’),
     Patients (pat_id, pat_name, pat_hosp_id) AS (
SELECT pat_id,
       pat_name,
       pat_hosp_id
FROM patient
JOIN hospital On(pat_hosp_id = hosp_id)
SELECT *
FROM patients;

How Vertica Evaluates WITH Clause?

Vertica can evaluate WITH condition in two ways:

Inline Expansion

This is the default evaluation method. Vertica database evaluates each WITH condition every time it is referenced by the primary query during execution.

Materialization

Vertica database evaluates each WITH condition only once, stores results in a temporary table, and references this table when query requires.

By default, materialization is disabled. You can enable this options at SESSION level or use ENABLE_WITH_CLAUSE_MATERIALIZATION at following of your WITH clause syntax.

Read:

For example;

WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ with-query...

Vertica WITH Usage Restrictions

Below are some of the WITH clause usage limitations in Vertica:

  • Each WITH clause query must be uniquely named.
  • WITH clauses do not support INSERT, UPDATE, or DELETE statements.
  • Vertica does not support recursive WITH clause.

Hope this helps 🙂

Tags: ,