13.5. Comparison Using Row Subqueries

For row subqueries, we can perform an equality comparison for all columns in a row. The subquery must return a single row. This method of comparison is not often used, but can provide some convenience for certain comparison operations. In the following example, we find the name of the capital of Finland. The query makes use of the fact that the city's name is stored in the City table, whereas the ID of a country's capital city is stored in the Country table:

 mysql> SELECT City.Name     -> FROM City     -> WHERE (City.ID, City.CountryCode) =     ->       (SELECT Capital, Code     ->        FROM Country     ->        WHERE Name='Finland'); +------------------------+ | Name                   | +------------------------+ | Helsinki [Helsingfors] | +------------------------+ 

Notice the use of the construct (City.ID, City.CountryCode). This creates a tuple of values and is known as a "row constructor." An equivalent method of defining a row is using ROW(), to underscore the fact that the values are used to construct a row of data for comparison. In this case, we would have written ROW(City.ID, City.CountryCode).

Trying to compare a tuple created by the row constructor with a subquery that returns several rows at once produces an error. The following example is similar to the preceding one, but does not work because there is no limit on the number of rows returned by the subquery:

 mysql> SELECT City.Name     -> FROM City     -> WHERE (City.ID, City.CountryCode) =     ->       (SELECT Capital, Code     ->        FROM Country); ERROR 1242 (21000): Subquery returns more than 1 row 

Row constructors can be used only for equality comparison using the = operator. You may not use other comparison operators such as <, >, or <>; nor may you use special words such as ALL, ANY, IN, or EXISTS.

Row constructors are commonly used with row subqueries, but they can be used in other contexts, and they may contain any type of scalar expression. For example, the following is a legal statement:

 mysql> SELECT Name, Population     -> FROM Country     -> WHERE (Continent, Region) = ('Europe', 'Western Europe'); +---------------+------------+ | Name          | Population | +---------------+------------+ | Netherlands   |   15864000 | | Belgium       |   10239000 | | Austria       |    8091800 | | Liechtenstein |      32300 | | Luxembourg    |     435700 | | Monaco        |      34000 | | France        |   59225700 | | Germany       |   82164700 | | Switzerland   |    7160400 | +---------------+------------+ 

In practice, row constructors are often inefficient when used like this, so it is more common to write the equivalent expression using AND. The query optimizer performs better if you write the WHERE clause like this:

 SELECT Name, Population FROM Country WHERE Continent = 'Europe' AND Region = 'Western Europe';