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...

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter14.

Derived tables

Have you ever used a nested SELECT (a SELECT in a table expression)? Of course, you have! Then, you've used a so-called derived table having the scope of the statement that creates it. Roughly, a derived table should be treated in the same way as a base table. In other words, it is advisable to give it and its columns meaningful names via the AS operator. This way, you can reference the derived table without ambiguity, and you'll respect the fact that most databases don't support unnamed (unaliased) derived tables.

jOOQ allows us to transform any SELECT in a derived table via asTable(), or its synonym table(). Let's have a simple example starting from this SELECT:

select(inline(1).as("one"));

This is not a derived table, but it can become one as follows (these two are synonyms):

Table<?> t = select(inline(1).as("one")).asTable();
Table<?> t = table(select(inline(1).as("one")));

In jOOQ, we...

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...

Handling views in jOOQ

The last section of this chapter is reserved for database views.

A view acts as an actual physical table that can be invoked by name. They fit well for reporting tasks or integration with third-party tools that need a guided query API. By default, the database vendor decides to materialize the results of the view or to rely on other mechanisms to get the same effect. Most vendors (hopefully) don't default to materializing views! Views should behave just like CTE or derived tables and should be transparent to the optimizer. In most cases (in Oracle), we would expect a view to be inlined, even when selected several times, because each time, a different predicate might be pushed down into the view. Actual materialized views are supported only by a few vendors, while the optimizer can decide to materialize the view contents when a view is queried several times. The view's definition is stored in the schema tables so it can be invoked by name wherever...

Summary

In this chapter, you've learned how to express derived tables, CTEs, and views in jOOQ. Since these are powerful SQL tools, it is very important to be familiar with them, therefore, besides the examples from this chapter, it is advisable to challenge yourself and try to solve more problems via jOOQ's DSL.

In the next chapter, we will tackle stored functions/procedures.