Inline derived tables
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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
-- 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();