New versions: Dev (3.15) | Latest (3.14) | 3.13

ANSI JOIN to table lists

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

Almost all SQL dialects support ANSI 92 JOIN syntax, but sometimes, users may wish to generate SQL that is compatible e.g. with older Oracle releases that did not yet support this syntax, or had limited optimiser support for them. As a workaround, the Settings.transformAnsiJoinToTableLists flag can be turned on to produce the following type of transformation:

-- Input
SELECT *
FROM a 
JOIN b ON a.id = b.id
-- Output
SELECT *
FROM a, b
WHERE a.id = b.id

This also works for OUTER JOIN:

-- Input
SELECT *
FROM a 
LEFT JOIN b ON a.id = b.id
-- Output
SELECT *
FROM a, b
WHERE a.id = b.id(+)

Example configuration

Settings settings = new Settings()
    .withTransformAnsiJoinToTableLists(true);

Feedback

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

The jOOQ Logo