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!