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);

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

Community