Common Table Expressions

Abstract

Common Table Expressions (CTE ) is an SQL feature is now common to all the major relational database products and was added to Informix Dynamic Server (IDS) in version 14.10.

Replay and slides from a recent Webinar on this by IBM are here.

This article describes the feature in a simpler way with complete real-world examples.

Content

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.

Results are:

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.

Caveats

This feature is new to the latest Informix version 14.10, so defects might exist.

Conclusion

CTEs are very easy to use for most purposes and should be considered when writing larger conventional SQL statements to make them simpler and clearer. They are also likely to be the best way to process hierarchical data without stored procedures or external programs. This article should be all you need, but other documentation on CTEs generally (not just Informix) will provide more detail.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.

Contact us

If you have any questions or would like to find out more, simply contact us.