COUNT(expr) scalar subquery comparison
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
When comparing a scalar subquery that calculates COUNT(expr) 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(expr) 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
Using Settings.transformPatternsScalarSubqueryCountExpressionGtZero, the following transformations can be achieved:
-- With Settings.transformPatternsScalarSubqueryCountExpressionGtZero active, this: SELECT (SELECT COUNT(col) FROM tab) > 0; -- ... is transformed into the equivalent expression: SELECT EXISTS (SELECT 1 FROM tab WHERE col IS NOT NULL);
Feedback
Do you have any feedback about this page? We'd love to hear it!