This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
COUNT(*) scalar subquery comparison
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
When comparing a scalar subquery that calculates COUNT(*) with a single value, then chances are that weaker optimisers might be better off with an equivalent EXISTS predicate as can be seen in this blog post about COUNT(*) vs EXISTS.
This transformation is only applied under certain circumstances, including:
- In the absence of
UNIONand other set operations - In the absence of
GROUP BYandHAVING - Only with
COUNT(*), not withCOUNT(expr)(see COUNT(expr) scalar subquery comparison for that case)
Using Settings.transformPatternsScalarSubqueryCountAsteriskGtZero, the following transformations can be achieved:
-- With Settings.transformPatternsScalarSubqueryCountAsteriskGtZero active, this: SELECT (SELECT COUNT(*) FROM tab) > 0; -- ... is transformed into the equivalent expression: SELECT EXISTS (SELECT 1 FROM tab);
Feedback
Do you have any feedback about this page? We'd love to hear it!