How to Write a Derived Table in jOOQ

One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table:

In SQL:

SELECT nested.*
FROM (
  SELECT AUTHOR_ID, count(*) books
  FROM BOOK
  GROUP BY AUTHOR_ID
) nested
ORDER BY nested.books DESC

In jOOQ:

// Declare the derived table up front:
Table<?> nested =
    select(BOOK.AUTHOR_ID, count().as("books"))
    .from(BOOK)
    .groupBy(BOOK.AUTHOR_ID).asTable("nested");

// Then use it in SQL:
ctx.select(nested.fields())
   .from(nested)
   .orderBy(nested.field("books"))
   .fetch();

And that’s pretty much it. The question usually arises from the fact that there’s a surprising lack of type safety when working with derived tables (or CTE, which are not that much different). Two things have to be understood:

1. Unlike SQL, the Java language can reference an object that hasn’t been declared yet, lexically, so we have to declare the derived table before using it.
2. Unlike generated code from the catalog, a derived table is just an expression, and there isn’t really a good way to add attributes to this expression, based on the expression’s structure, at least not in Java. That means that columns of a derived table aren’t dereferenceable in a type safe way. You can, however, reuse expressions, as shown below:

// Declare a field expression up front:
Field<Integer> count = count().as("books");

// Then use it in the derived table:
Table<?> nested =
    select(BOOK.AUTHOR_ID, count)
    .from(BOOK)
    .groupBy(BOOK.AUTHOR_ID).asTable("nested");

// And use it as well in the outer query, when dereferencing a column:
ctx.select(nested.fields())
   .from(nested)
   .orderBy(nested.field(count))
   .fetch();

Did you really need the derived table?

A lot of times when I answer such questions on Stack Overflow or elsewhere, it becomes apparent that the derived table wasn’t needed in the first place. In fact, this very example from the jOOQ manual didn’t need any derived table! The SQL query can be simplified to this:

SELECT AUTHOR_ID, count(*) books
FROM BOOK
GROUP BY AUTHOR_ID
ORDER BY books DESC

Nothing is lost by this simplification. Seeing when a simplification can take place might require some practice. It’s always good to be mindful of the logical order of operations in SQL, to make sure the resulting query is equivalent. But when it is, then it’s much easier to translate to jOOQ, because now, we can use generated code again, everywhere, and don’t have to rely on the less type safe dereferencing of columns from derived tables. Here’s the jOOQ equivalent:

// We can still assign expressions to local variables
Field<Integer> count = count().as("books");

// And then use them in the query:
ctx.select(BOOK.AUTHOR_ID, count)
   .from(BOOK)
   .groupBy(BOOK.AUTHOR_ID)
   .orderBy(count)
   .fetch();

Conclusion

So, when you work with jOOQ and your query is sufficiently simple, then your question of

How to write a derived table in jOOQ?

Might be changed to:

Did I need a derived table in the first place?

That way, you can improve both your jOOQ query and your SQL query