The jOOQ User Manual : SQL building : QueryParts : SQL transformation : Pattern based transformation : COUNT(*) scalar subquery comparison | previous : next |
This is experimental functionality, and as such subject to change. Use at your own risk!
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
UNION
and other set operations - In the absence of
GROUP BY
andHAVING
- Only with
COUNT(*)
, not withCOUNT(expr)
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) -- (SELECT COUNT(*) FROM tab) > 0 ;
Feedback
Do you have any feedback about this page? We'd love to hear it!