The jOOQ User Manual SQL building QueryParts SQL transformation Pattern based transformation Unnecessary EXISTS subquery clauses

previous : next

Unnecessary EXISTS subquery clauses

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

The EXISTS predicate can safely ignore a few of its subquery clauses without changing semantics, including:

• The SELECT clause
• The DISTINCT clause
• The ORDER BY clause
• The LIMIT clause, if it is non-zero

All of the above applies only if there are no aggregate functions in the EXISTS subquery.

Using Settings.transformPatternsUnnecessaryExistsSubqueryClauses, the following transformations can be achieved:

-- With Settings.transformPatternsUnnecessaryExistsSubqueryClauses active, this:
SELECT EXISTS (SELECT DISTINCT a, b FROM t);

-- ... is transformed into the equivalent expression:
SELECT EXISTS (SELECT 1 FROM t);

The jOOQ User Manual SQL building QueryParts SQL transformation Pattern based transformation Unnecessary EXISTS subquery clauses

previous : next