Chapter 14: Derived Tables, CTEs, and Views

Derived tables, CTEs, and views are important players in the SQL context. They're useful to organize and optimize the reuse of long and complex queries – typically, base queries and/or expensive queries (in performance terms), and to improve readability by breaking down the code into separate steps. Mainly, they link a certain query to a name, possibly stored in the schema. In other words, they hold the query text, which can be referenced and executed via the associated name when needed. If results materialize, then the database engine can reuse these cached results, otherwise, they have to be recomputed at each call.

Derived tables, CTEs, and views have specific particularities (including database vendor-specific options), and choosing between them is a decision that strongly depends on the use case, the involved data and queries, the database vendor and optimizer, and so on. As usual, we handle this topic from the jOOQ perspective...