13.2. Subqueries as Scalar Expressions

Scalar subqueries can appear almost anywhere that a scalar value is allowed by the SQL syntax. This means that you can use subqueries as function parameters, use mathematical operators on subqueries that contain numeric values, and so forth. The following example shows how to use a scalar subquery as a parameter to the CONCAT() function:

 mysql> SELECT CONCAT('The country code for Finland is: ',     ->              (SELECT Code     ->               FROM Country     ->               WHERE Name='Finland')) AS s1; +--------------------------------------+ | s1                                   | +--------------------------------------+ | The country code for Finland is: FIN | +--------------------------------------+ 

Notice that the subquery must be enclosed in parentheses here, just as in any other context where a subquery may appear.

The next example shows the use of scalar subqueries in a mathematical expression that calculates the ratio of the people living in cities to that of the world population:

 mysql> SELECT (SELECT SUM(Population) FROM City) /     ->        (SELECT SUM(Population) FROM Country) AS ratio; +-------+ | ratio | +-------+ |  0.24 | +-------+ 

A scalar subquery result can be assigned to a user variable for later use. The previous example can be written with user variables as follows:

 SET @city_pop = (SELECT SUM(Population) FROM City); SET @country_pop = (SELECT SUM(Population) FROM Country); SELECT @city_pop / @country_pop; 

There are some contexts in which scalar subqueries are not allowed. You cannot use a scalar subquery when a literal value is required, such as for an argument in a LIMIT clause.