Available in versions: Dev (3.18) | Latest (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);

Feedback

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

The jOOQ Logo