Netezza WITH Clause Syntax and Examples

If you have requirement to reuse the piece of query result in same query, then you can use the Netezza WITH clause. You can also use the WITH clause to improve the speed for Netezza complex sub-queries and improve overall performance. This is also called the sub query factoring and you can use it when sub query is called multiple times. You can use the WITH Clause command to run multiple sub queries in a SELECT statement.

The main advantage of Netezza 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, UPDATE, DELETE, CTAS and SELECT.

The other databases like Teradata supports creating RECURSIVE using WITH clause but Netezza does not support RECURSIVE or hierarchical query.

Netezza WITH Clause Syntax

Below is the syntax for WITH clause:

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

Netezza WITH clause Example

Let’s consider the example of hospital and patients:

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;

Netezza WITH clause in INSERT statement

You can use the WITH clause while inserting data to table. For example:

TRAINING.ADMIN(ADMIN)=> INSERT INTO test_tbl WITH sample_rec AS (select * from table) SELECT * FROM sample_rec;

Netezza WITH clause in UPDATE statement

You can use the WITH clause while updating data from table. For example:

TRAINING.ADMIN(ADMIN)=> UPDATE test_tbl SET sample_id = 123 where id = ( WITH sample_rec AS (select * from table) SELECT * FROM sample_rec where id is null) ;

Netezza WITH clause in DELETE statement

You can use the SQL WITH clause while deleting data from table. For example:

TRAINING.ADMIN(ADMIN)=> DELETE from test_tbl where id IN ( WITH sample_rec AS (select * from table where id is null ) SELECT * FROM sample_rec );