A query is a component of certain SQL statements; it specifies a (temporary) result table.
A table expression creates a temporary result table from a simple query. Clauses further refine the result table. For example, you can use a table expression as a query to select all of the managers from several departments, specify that they must have over 15 years of working experience, and be located at the New York branch office.
A common table expression is like a temporary view within a complex query. It can be referenced in other places within the query, and can be used in place of a view. Each use of a specific common table expression within a complex query shares the same temporary view.
Recursive use of a common table expression within a query can be used to support applications such as airline reservation systems, bill of materials (BOM) generators, and network planning.
A WITH statement is composed of one or more common table expressions and a SELECT statement. A common table expression defines a named results table that can be specified as a table in the FROM clause of a subsequent SELECT statement. You can create WITH statements only if you are using IBM DB2 database.
You can use a common table expression in the following ways:
In place of a view to avoid creating the view (when general use of the view is not required and the SELECT statement does not use positioned updates or deletes)
To enable grouping by a column that is derived from a scalar SUBSELECT statement or function that is not deterministic or has external actions
When the results table is based on host variables
When the same results table must be shared in a FULLSELECT
When the result must be derived by using recursion
The syntax used to construct a common table expression is:
WITH [table-name]<([column-name], ...])> AS([select-statement])
table-name - Specifies the name that is to be assigned to the temporary table to be created.
column-name - Specifies the name(s) to be assigned to one or more columns that are to be included in the temporary table to be created. Each column name specified must be unique and unqualified; if no column names are specified, the names derived from the result data set produced by the select-statement specified will be used. If a list of column names is specified, the number of column names provided must match the number of columns that will be returned by the SELECT statement used to create the temporary table. If a common table expression is recursive, or if the result data set produced by the SELECT statement specified contains duplicate column names, column names must be specified.
select-statement - Identifies a SELECT SQL statement that, when executed, will produce the data values to be added to the column(s) in the temporary table to be created.