Optional conditional tables
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A key capability when creating dynamic SQL queries is to be able to provide optional table expressions.
boolean condition = ...
create.select(BOOK.ID)
.from(BOOK)
.join(condition ? AUTHOR : noTable()).on(BOOK.AUTHOR_ID.eq(AUTHOR_ID))
.fetch();
The above query produces:
-- If condition is true SELECT book.id FROM book JOIN author ON book.author_id = author.id -- If condition is false SELECT book.id FROM book
The noTable() expression will be ignored. If that means the FROM clause is empty, then the entire clause will be omitted. Depending on the JOIN type, an ON clause may still be required, syntactically. If noTable() is supplied, however, the ON clause has no effect and will be ignored.
ThenoTable()expression is supported only in the DSL API, not in the model API, where the behaviour ofnoTable()is undefined.
Feedback
Do you have any feedback about this page? We'd love to hear it!