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

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

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.

The noTable() expression is supported only in the DSL API, not in the model API, where the behaviour of noTable() is undefined.

Feedback

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

The jOOQ Logo