Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
BETWEEN predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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)
Dialect support
This example using jOOQ:
BOOK.TITLE.between("E").and("K")
Translates to the following dialect specific expressions:
-- All dialects BOOK.TITLE BETWEEN 'E' AND 'K'
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
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)
Dialect support
This example using jOOQ:
BOOK.TITLE.betweenSymmetric("K").and("E")
Translates to the following dialect specific expressions:
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DB2, DERBY, DUCKDB, FIREBIRD, H2, HANA, INFORMIX, MARIADB, MEMSQL, MYSQL, ORACLE, -- REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA ( BOOK.TITLE BETWEEN 'K' AND 'E' OR BOOK.TITLE BETWEEN 'E' AND 'K' ) -- AURORA_POSTGRES, COCKROACHDB, EXASOL, HSQLDB, POSTGRES, YUGABYTEDB BOOK.TITLE BETWEEN SYMMETRIC 'K' AND 'E'
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!