The simplest use of a subquery is one in which it produces a single scalar value, as in this example:

The subquery, sometimes also referred to as an inner query, is written inside parentheses to distinguish it from the outer query.

This particular example may seem trivial, but it forms the basis for some more useful possibilities, as we’ll see shortly. You should note that a subquery used in this manner (with = or any other relational operator that compares scalar values) must return a single value; otherwise an error will result, as shown here:

If the categories table contained only one row, no error would result from dropping the WHERE clause of the subquery.

Subqueries can also be used in expressions and with SQL functions, as shown in the three examples in the next illustration. In the third of these example queries, you can see that they may also be aliased.

The real power of subqueries begins to become more apparent when we start using them in WHERE clauses. Here’s a simple instance that illustrates a very common scenario:

In the second of the two example queries just shown, we do a join on the categories table in order to obtain the category name. You should notice that the inner query doesn’t require the use of the table aliases from the outer query; it’s autonomous in that respect, as the outer query is concerned only with the value returned by the inner query, and not with any of the tables or columns referenced by the inner query.

It’s also possible in MySQL to reverse the order of the subquery yielding the scalar value and the value you’re comparing it to:

However, you should be aware that this isn’t part of the SQL standards and may not work in other databases, so if portability is a concern, use the value or column name first, followed by the subquery.

There are other ways to accomplish this particular task without the use of the subquery. One of these is to store an intermediate result in application code or to employ a MySQL user variable for the same purpose, like so:

SELECT @pmax := MAX(price) FROM products; SELECT c.name AS category, p.name AS product, p.price
FROM products p
JOIN categories c
ON c.id = p.category_id
WHERE id = @pmax;

Alternatively, you could write something like this:

SELECT c.name AS category, p.name AS product, p.price
FROM products p
JOIN categories c
ON c.id = p.category_id
ORDER BY p.price DESC LIMIT 1;

This at least has the advantage of being a single query. However, neither of these methods is as easy to read and understand as when we use the subquery. There’s also the problem of what happens when more than one product has the maximum price.

You’re not limited to testing for equality in such cases; you can use any comparison that’s appropriate to the type of data returned by the subquery. Suppose you want to promote those products having greater than the average price of all products, and you’d like a list with the names and IDs of the products and their prices. It would also be helpful to have the product categories listed as well. This will require another join on the products and categories tables, with a nested subquery in the WHERE clause, as shown here:

In this example, we did a greater-than test, and the value returned by the subquery was obtained by using the AVG() function within the subquery.

Subqueries That Return Sets

In the last example from the previous section, you may notice that we were still testing against a single scalar value. In the current scenario, this is helpful when we want to compare prices of products against the average price for all products or the price of the largest product overall. How about finding the most expensive product in each category? It turns out that you can also make comparisons against subqueries that return sets of values using the IN, ANY, and SOME operators. In previous versions of MySQL, these operators were useful only with a set of values that you supplied directly. Beginning in MySQL 4.1, the set can be the result of another query.

---

NOTE  Remember that the operator IN is equivalent to = ANY or = SOME. We discussed this in Chapter 4; see the “Operators for Working with Sets” section of that chapter if you need to refresh your memory.

---

When using IN or its synonyms, you can put GROUP BY and HAVING clauses in the subquery, unlike the case with equality and other operators that make direct comparisons between scalar values. Notice that the set must still be one-dimensional; that is, it can return results only from a single column.