Teradata WITH Clause Syntax, Usage and Examples

  • Post author:
  • Post last modified:February 26, 2018
  • Post category:General
  • Reading time:2 mins read

Teradata WITH Clause is an optional clause that always precedes SELECT clause in the query statements. 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).

Teradata WITH Clause

WITH clause is used for many purposes, if you want to find our hierarchy in the data then recursive WITH clause is used. If your requirement is to reuse the piece of query result in same query construct, then you can use the Teradata WITH clause. WITH clause does improves the performance by removing overhead of creating and populating temporary tables.

Also Read:

The main advantage of Teradata WITH clause is that, you can use it wherever SELECT clause is acceptable in the SQL script or query. WITH clause is always associated with SELECT statements.

For instance, you can use it in:

  • INSERT INTO … SELECT
  • CTAS
  • SELECT

Teradata WITH Clause Syntax

Below is the syntax for Teradata WITH clause:

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

Teradata WITH Clause Examples

Using WITH clause

WITH CTE1 AS (select current_date as today)
select today
from CTE1;

Terdata WITH Clause in an INSERT Statement Example

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

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

Teradata RECURSIVE Query using WITH Clause

Below is the Recursive query for finding employee hierarchy:

WITH RECURSIVE temp_table (employee_number) 
AS (SELECT root.employee_number 
FROM employee AS root 
WHERE root.manager_employee_number = 801 
UNION ALL 
SELECT indirect.employee_number 
FROM temp_table AS direct, employee 
AS indirect WHERE direct.employee_number = indirect.manager_employee_number ) 
SELECT * FROM temp_table ORDER BY employee_number;
Tags: