Available in versions: Dev (3.20) | Latest (3.19)

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.

Feedback

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

The jOOQ Logo