This is experimental functionality, and as such subject to change. Use at your own risk!
Unnecessary scalar subquery
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A scalar subquery that contains only a projection is unnecessary. While it is unlikely for users to implement such projection only scalar subqueries, they may appear as a result of other transformations. This rule excludes scalar subqueries:
- Containing aggregate functions
- Containing window functions
- Contained in the GROUP BY clause (
GROUP BY (SELECT 1)may have a different meaning fromGROUP BY 1) - Contained in the ORDER BY clause (
ORDER BY (SELECT 1)has a different meaning fromORDER BY 1)
Using Settings.transformPatternsUnnecessaryScalarSubquery, the following transformations can be achieved:
-- With Settings.transformPatternsUnnecessaryScalarSubquery active, this: SELECT (SELECT a) FROM t WHERE (SELECT a) = b; -- ... is transformed into the equivalent expression: SELECT a FROM t WHERE a = b;
Feedback
Do you have any feedback about this page? We'd love to hear it!