Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15

IN condition subquery with LIMIT to derived table

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

Some dialects do not support a LIMIT clause in a subquery of an IN predicate. jOOQ can transform such a subquery to an equivalent derived table:

-- Input
SELECT *
FROM t
WHERE id IN (SELECT id FROM u ORDER BY id LIMIT 5)
-- Output
SELECT *
FROM t
WHERE id IN (SELECT * FROM (SELECT id FROM u ORDER BY id LIMIT 5) AS u)

Example configuration

Settings settings = new Settings()
    .setTransformInConditionSubqueryWithLimitToDerivedTable(Transformation.WHEN_NEEDED);

References to this page

Feedback

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

The jOOQ Logo