SQL transformation

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ supports a few built in SQL transformations, which can be enabled through a variety of settings. These transformations will produce transformed SQL output regardless if the input was created using the DSL API, or the parser.

Table of contents

3.23.9.1.

ANSI JOIN to table lists

3.23.9.2.

Table lists to ANSI JOIN

3.23.9.3.

ROWNUM to LIMIT

3.23.9.4.

QUALIFY to derived table

3.23.9.5.

IN condition subquery with LIMIT to derived table

3.23.9.6.

Unnecessary arithmetic expressions

3.23.9.7.

Pattern based transformation (new)

3.23.9.7.1.

AND to NOT IN (new)

3.23.9.7.2.

Arithmetic expressions (new)

3.23.9.7.3.

COUNT(*) scalar subquery comparison (new)

3.23.9.7.4.

COUNT(expr) scalar subquery comparison (new)

3.23.9.7.5.

Empty scalar subquery (new)

3.23.9.7.6.

Hyperbolic functions (new)

3.23.9.7.7.

Idempotent function repetition (new)

3.23.9.7.8.

Inverse hyperbolic functions (new)

3.23.9.7.9.

Logarithmic functions (new)

3.23.9.7.10.

Merge AND predicates (new)

3.23.9.7.11.

Merge BIT_NOT with BIT_NAND (new)

3.23.9.7.12.

Merge BIT_NOT with BIT_NOR (new)

3.23.9.7.13.

Merge BIT_NOT with BIT_XNOR (new)

3.23.9.7.14.

Merge IN predicates (new)

3.23.9.7.15.

Merge NOT with comparison predicates (new)

3.23.9.7.16.

Merge NOT with DISTINCT predicate (new)

3.23.9.7.17.

Merge OR predicates (new)

3.23.9.7.18.

Merge range predicates (new)

3.23.9.7.19.

Normalise associative operations (new)

3.23.9.7.20.

Normalise fields compared to values (new)

3.23.9.7.21.

Normalise IN list with single element to comparison (new)

3.23.9.7.22.

OR to IN (new)

3.23.9.7.23.

Repeated arithmetic negation (new)

3.23.9.7.24.

Repeated bitwise negation (new)

3.23.9.7.25.

Repeated NOT (new)

3.23.9.7.26.

Trigonometric functions (new)

3.23.9.7.27.

Trim (new)

3.23.9.7.28.

Trivial case abbreviations (new)

3.23.9.7.29.

Trivial predicates (new)