Use the WITH clause to improve query speed for complex subqueries, without the need for conversion. This is also called subquery factoring, and is used when a subquery is started multiple times.
The WITH clause syntax allows it to be used wherever the SELECT syntax was acceptable in the past (INSERT, UPDATE, DELETE, CTAS, and SELECT).
Recursive queries for the WITH Clause are not supported.
Before downgrading to an IBM® Netezza® system version that does not support the With Clause syntax, all SQL objects (views and stored procedures) that use this new syntax must be removed from the system.
<query expression > ::=
[ <WITH clause>] <query expression body>;
<with list> ::=
<with list element> [ { <comma> <with list element> }… ]
<with list element> ::=
<query name> [ <left paren> <with column list> <right paren> ]
AS <left paren> <query expression> <right paren> [ <search or
cycle clause> ]
<with column list> ::= <column name list>
<query expression body> ::=
<query term>
| <query expression body> UNION [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>
| <query expression body> EXCEPT [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>
<query term> ::=
<query primary>
| <query term> INTERSECT [ ALL | DISTINCT ]
[ <corresponding spec> ] <query primary>
<query primary> ::=
<simple table>
| <left paren> <query expression body> <right paren>
<simple table> ::=
<query specification>
| <table value constructor>
| <explicit table>
<explicit table> ::= TABLE <table or query name>
<corresponding spec> ::=
CORRESPONDING [ BY <left paren> <corresponding column list> <right
paren> ]
<corresponding column list> ::= <column name list>
Input | Description |
---|---|
<query name> | The name that is given to the query expression. Multiple query name and expression combinations can be expressed, separated by a comma. |
<expression> | The name of a table column or an expression. |
Output | Description |
---|---|
ROWS | Returns the complete set of rows that result from the query. |
COUNT | Returns the number of rows that are returned by the query. |
ERROR: Not Supported | This usage is not currently supported in the system. |
MYDB.SCH1(USER)=> INSERT INTO emp_copy WITH employee AS (select * from
emp) SELECT * FROM employee;
MYDB.SCH1(USER)=> UPDATE emp_copy SET grp = 'gone' WHERE id =
(WITH employee AS (select * from emp) SELECT id FROM employee WHERE id
= 1);
MYDB.SCH1(USER)=> DELETE FROM emp_copy WHERE id IN
(WITH employee AS (SELECT * FROM emp_copy where grp = 'gone')
SELECT id FROM employee);
WITH manager (mgr_id, mgr_name, mgr_dept) AS
(SELECT id, name, grp
FROM emp_copy
WHERE mgr = id AND grp != 'gone'),
employee (emp_id, emp_name, emp_mgr) AS
(SELECT id, name, mgr_id
FROM emp_copy JOIN manager ON grp = mgr_dept),
mgr_cnt (mgr_id, mgr_reports) AS
(SELECT mgr, COUNT (*)
FROM emp_copy
WHERE mgr != id
GROUP BY mgr)
SELECT *
FROM employee JOIN manager ON emp_mgr = mgr_id JOIN mgr_cnt
WHERE emp_id != mgr_id
ORDER BY mgr_dept;