Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12

Inline derived tables

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

An inline derived table is a Table expression with a WHERE clause, both of which can be inlined into the calling query in a lot of cases.

The main use-case for this feature is dynamic SQL, where you can add predicates to tables without call sites noticing, and without giving up on type safety.

Code generation support for inline derived tables was added in jOOQ 3.19, only. The following examples dereferenece columns in a type safe way from generated code. Earlier versions of jOOQ require using Table.field(Field), or a similar method.
-- In SQL, the derived table is inlined
SELECT
  BOOK.ID,
  BOOK.TITLE
FROM BOOK
WHERE BOOK.TITLE LIKE 'A%'
// This book reference can be supplied dynamically
Book aBooks = BOOK.where(BOOK.TITLE.like("A%"));

create.select(aBooks.ID, aBooks.TITLE)
      .from(aBooks)
      .fetch();

Whenever the context requires, the inline derived table generates an explicit derived table, e.g. when used in a LEFT JOIN:

-- In SQL, the derived table created explicitly
SELECT BOOK.ID, BOOK.TITLE
FROM AUTHOR
LEFT JOIN (
  SELECT * FROM BOOK WHERE BOOK.TITLE LIKE 'A%'
) BOOK
ON AUTHOR.ID = BOOK.AUTHOR_ID
// This book reference can be supplied dynamically
Book aBooks = BOOK.where(BOOK.TITLE.like("A%"));

create.select(AUTHOR.ID, aBooks.ID, aBooks.TITLE)
      .from(AUTHOR)
      .leftJoin(aBooks).on(AUTHOR.ID.eq(aBooks.AUTHOR_ID))
      .fetch();

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo