All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6

The BETWEEN predicate can be seen as syntactic sugar for a pair of comparison predicates. According to the SQL standard, the following two predicates are equivalent:

[A] BETWEEN [B] AND [C]
[A] >= [B] AND [A] <= [C]

Note the inclusiveness of range boundaries in the definition of the BETWEEN predicate. Intuitively, this is supported in jOOQ as such:

PUBLISHED_IN     BETWEEN 1920 AND 1940
PUBLISHED_IN NOT BETWEEN 1920 AND 1940
BOOK.PUBLISHED_IN.between(1920).and(1940)
BOOK.PUBLISHED_IN.notBetween(1920).and(1940)

BETWEEN SYMMETRIC

The SQL standard defines the SYMMETRIC keyword to be used along with BETWEEN to indicate that you do not care which bound of the range is larger than the other. A database system should simply swap range bounds, in case the first bound is greater than the second one. jOOQ supports this keyword as well, emulating it if necessary.

PUBLISHED_IN     BETWEEN SYMMETRIC 1940 AND 1920
PUBLISHED_IN NOT BETWEEN SYMMETRIC 1940 AND 1920
BOOK.PUBLISHED_IN.betweenSymmetric(1940).and(1920)
BOOK.PUBLISHED_IN.notBetweenSymmetric(1940).and(1920)

The emulation is done trivially:

[A] BETWEEN SYMMETRIC [B] AND [C]
([A] BETWEEN [B] AND [C]) OR ([A] BETWEEN [C] AND [B])
The jOOQ Logo