13.1. Types of Subqueries | MySQL 5.0 Certification Study Guide

In this chapter, we divide subqueries into four general categories, which affect the contexts in which they can be used:

Scalar subqueries return a single value; that is, one row with one column of data.
Row subqueries return a single row with one or more columns of data.
Column subqueries return a single column with one or more rows of data.
Table subqueries return a result with one or more rows containing one or more columns of data.

The following example shows how a simple subquery works. We use the two tables Country and CountryLanguage from the world database to find the languages spoken in Finland:

 mysql> SELECT Language     -> FROM CountryLanguage     -> WHERE CountryCode = (SELECT Code     ->                      FROM Country     ->                      WHERE Name='Finland'); +----------+ | Language | +----------+ | Estonian | | Finnish  | | Russian  | | Saame    | | Swedish  | +----------+

The following statement uses a subquery to determine which country has the most populous city in the world:

 mysql> SELECT Country.Name     -> FROM   Country, City     -> WHERE  Country.Code = City.CountryCode     ->   AND  City.Population = (SELECT MAX(Population)     ->                           FROM City); +-------+ | Name  | +-------+ | India | +-------+

As you will undoubtedly notice in many of the descriptions and examples in this section, many uses of subqueries can be rewritten to completely equivalent (and often more efficient) queries using joins. Nonetheless, subqueries are preferred by many as an alternative way of specifying relations that otherwise requires complex joins or unions. Some users insist on using subqueries simply because they find them much more readable and easier to maintain than queries involving complex joins. Reasons to convert a subquery to a join are that the join may be more efficient than the equivalent subquery, or you might need to run a query using an older version of MySQL that does not support subqueries. (Subquery support was added in MySQL 4.1.)