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