In this chapter, we divide subqueries into four general categories, which affect the contexts in which they can be used:
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.) |