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> ...)

For example:

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 syntax:

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 WHERE 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.


Comments loading...