The documentation page here says:
“A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement.”
It is declared at the front of the statement that will use it using the new “WITH” keyword:
The primary purpose is to simplify the SQL that follows it by moving out a sub-query into a separate object to reduce complexity or repetition.
For example, the following query (without a CTE) lists users connected to the IDS instance for more than 30 minutes, and the conversion of “connected” to “duration” is needed twice:
We could avoid duplicating the conversion logic with a Common Table Expression:
An alternative is to use a “derived table” or “table expression” as below which has been possible since IDS 11.10:
Example results in all cases are:
- All these three queries produce the same query plan as captured with SET EXPLAIN ON.
- A view could be created for the sub-query but requires DBA privileges and is unnecessary unless used elsewhere.
- CTEs really come into their own when they are used more than once in the subsequent SQL statement.
The full syntax from the documentation page here is:
- You can declare more than one CTE separated by commas.
- Column names can be stated in brackets if ambiguous (exactly like a derived table or view).
- The subsequent SQL statement can also be an INSERT, UPDATE, or DELETE.
Most complexity in descriptions of CTEs elsewhere relate to recursion. In the real world, this relates to hierarchical tables with rows joining to other rows in the same table, such as organisation charts or stock kits & components. You will probably seldom need recursion, but CTEs might be the best solution if you do. To experiment with this, an example has been adapted from Microsoft SQL Server documentation here.
Create and populate this test table:
With that data, the following demonstrates more than one CTE, a CTE being referenced more than once, and recursion:
The documentation page here states:
“A recursive CTE starts with either one non-recursive sub-query or several non-recursive sub-queries joined by UNION or UNION ALL and ends with exactly one recursive sub-query joined by UNION ALL. A recursive sub-query references the CTE being defined.”
The two optional lines above safeguard against infinitely repeating cyclical data relationships. Such a CYCLE clause adds an extra column with your choice of name (“cyclic” in this example) to a recursive CTE result set purely for this purpose.
Note that recursion can be achieved in IDS 11.50 onwards with a Hierarchical Clause (CONNECT BY) in a SELECT statement, but IBM say here that a CTE is over 10 times faster.