8.3 Converting Subqueries to Joins
Standard SQL allows a SELECT statement to contain a nested SELECT, which is known as a subquery. MySQL implements subqueries as of version 4.1. For MySQL 4.0, subqueries sometimes can be rewritten as joins, which provides a workaround for lack of subqueries in many cases. A join could also be more efficient than an equivalent statement expressed as a subquery.
A subquery that finds matches between tables often can be rewritten as an inner join. A subquery that finds mismatches often can be rewritten as an outer join.
8.3.1 Converting Subqueries to Inner Joins
One form of SELECT that uses subqueries finds matches between tables. For example, an IN subquery that identifies countries for which languages are listed in the CountryLanguage table looks like this:
mysql> SELECT Name FROM Country
-> WHERE Code IN (SELECT CountryCode FROM CountryLanguage);
+---------------------------------------+
| Name |
+---------------------------------------+
| Afghanistan |
| Netherlands |
| Netherlands Antilles |
| Albania |
| Algeria |
| American Samoa |
| Andorra |
| Angola |
| Anguilla |
| Antigua and Barbuda |
| United Arab Emirates |
| Argentina |
...
To convert this into an inner join, do the following:
Move the CountryLanguage table named in the subquery to the FROM clause.
The WHERE clause compares the Code column to the country codes returned from the subquery. Convert the IN expression to an explicit direct comparison between the country code columns of the two tables.
These changes result in the following inner join:
mysql> SELECT Name FROM Country, CountryLanguage
-> WHERE Code = CountryCode;
+---------------------------------------+
| Name |
+---------------------------------------+
| Afghanistan |
| Afghanistan |
| Afghanistan |
| Afghanistan |
| Afghanistan |
| Netherlands |
| Netherlands |
| Netherlands |
| Netherlands |
| Netherlands Antilles |
| Netherlands Antilles |
| Netherlands Antilles |
...
Note that this output is not quite the same as that from the subquery, which lists each matched country just once. The output from the join lists each matched country once each time its country code occurs in the CountryLanguage table. To list each name just once, as in the subquery, add DISTINCT to the join:
mysql> SELECT DISTINCT Name FROM Country, CountryLanguage
-> WHERE Code = CountryCode;
+---------------------------------------+
| Name |
+---------------------------------------+
| Afghanistan |
| Netherlands |
| Netherlands Antilles |
| Albania |
| Algeria |
| American Samoa |
| Andorra |
| Angola |
| Anguilla |
| Antigua and Barbuda |
| United Arab Emirates |
| Argentina |
...
8.3.2 Converting Subqueries to Outer Joins
Another form of SELECT that uses subqueries finds mismatches between tables. For example, a NOT IN subquery that identifies countries for which no languages are listed in the CountryLanguage table looks like this:
mysql> SELECT Name FROM Country
-> WHERE Code NOT IN (SELECT CountryCode FROM CountryLanguage);
+----------------------------------------------+
| Name |
+----------------------------------------------+
| Antarctica |
| Bouvet Island |
| British Indian Ocean Territory |
| South Georgia and the South Sandwich Islands |
| Heard Island and McDonald Islands |
| French Southern territories |
+----------------------------------------------+
This subquery can be rewritten as an outer join. For example, to change the preceding subquery into a left join, modify it as follows:
Move the CountryLanguage table named in the subquery to the FROM clause and join it to Country using LEFT JOIN.
The WHERE clause compares the Code column to the country codes returned from the subquery. Convert the IN expression to an explicit direct comparison between the country code columns of the two tables in the FROM clause.
In the WHERE clause, restrict the output to those rows having NULL in the CountryLanguage table column.
These changes result in the following LEFT JOIN:
mysql> SELECT Name
-> FROM Country LEFT JOIN CountryLanguage ON Code = CountryCode
-> WHERE CountryCode IS NULL;
+----------------------------------------------+
| Name |
+----------------------------------------------+
| Antarctica |
| Bouvet Island |
| British Indian Ocean Territory |
| South Georgia and the South Sandwich Islands |
| Heard Island and McDonald Islands |
| French Southern territories |
+----------------------------------------------+
Because any left join may be written as an equivalent right join, the subquery also can be written as a right join:
mysql> SELECT Name
-> FROM CountryLanguage RIGHT JOIN Country ON CountryCode = Code
-> WHERE CountryCode IS NULL;
+----------------------------------------------+
| Name |
+----------------------------------------------+
| Antarctica |
| Bouvet Island |
| British Indian Ocean Territory |
| South Georgia and the South Sandwich Islands |
| Heard Island and McDonald Islands |
| French Southern territories |
+----------------------------------------------+
| 
