Exploring Common Table Expressions (CTEs) in jOOQ
CTEs are represented by the SQL-99 WITH clause. You already saw several examples of CTE in previous chapters, for instance, in Chapter 13, Exploiting SQL Functions, you saw a CTE for computing z-scores.
Roughly, via CTEs, we factor out the code that otherwise should be repeated as derived tables. Typically, a CTE contains a list of derived tables placed in front of a SELECT statement in a certain order. The order is important because these derived tables are created conforming to this order and a CTE element can reference only prior CTE elements.
Basically, we distinguish between regular (non-recursive) CTEs and recursive CTEs.
Regular CTEs
A regular CTE associates a name to a temporary result set that has the scope of a statement such as SELECT, INSERT, UPDATE, DELETE, or MERGE (CTEs for DML statements are very useful vendor-specific extensions). But, a derived table or another type of subquery can have its own CTE as...
