Set functions in Subqueries
This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.
Who makes more than the average salary? What is the cheapest book? That's the kind of question that requires a comparison with a subquery that contains a set function. In other words, you need a condition with this general format:
... WHERE <value> <comparison-operator> (SELECT <set function> ...)
SELECT ... FROM ... WHERE ... = (SELECT MIN(price) FROM book);
This is one of the cases where it's much easier to use a subquery than a join.
If you try to do something more complex, you will probably run into some
restrictions because set functions within subqueries are hard to implement.
We'll give just one example of a restriction (from SQL-92)
— if the set function's argument is an "outer
reference" Column (i.e.: the name of a Column in the outer enclosing query),
then that must be the only <Column reference> within that argument and the set
function has to appear either in a select list or within a subquery that
belongs to a
HAVING clause. For example, this complex query uses illegal
SELECT * FROM Table_1 WHERE 0 = ( SELECT SUM(Table_1.column_1+Table_2.column_1) FROM Table_2);
It would be even more illegal if the set function appeared in the
clause, or if there was a third level of subquery nesting. Nobody can remember
such a complex rule, and there are more like it, so the cautious programmer
simply avoids trying anything fancy when using set functions within subqueries.