13.7. 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 and earlier, subqueries sometimes can be rewritten as joins, which provides a workaround for lack of subqueries in many cases. Even for MySQL 4.1 and up, a join might be handled by the optimizer more efficiently 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. The following sections describe how to do this.

13.7.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:

1.

Move the CountryLanguage table named in the subquery to the FROM clause.

2.

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                             | ... 

13.7.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:

1.

Move the CountryLanguage table named in the subquery to the FROM clause and join it to Country using LEFT JOIN.

2.

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.

3.

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                  | +----------------------------------------------+ 

Any left join may be written as an equivalent right join, so 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                  | +----------------------------------------------+ 



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net