Selecting from Multiple Tables

You're not limited to selecting only one table at a time. That would certainly make application programming a long and tedious task! When you select from more than one table in one SELECT statement, you are really joining the tables together.

Suppose you have two tables: fruit and color. You can select all rows from each of the two tables by using two separate SELECT statements:

mysql> SELECT * FROM fruit; +----+-----------+ | id | fruitname | +----+-----------+ |  1 | apple     | |  2 | orange    | |  3 | grape     | |  4 | banana    | +----+-----------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM color; +----+-----------+ | id | colorname | +----+-----------+ |  1 | red       | |  2 | orange    | |  3 | purple    | |  4 | yellow    | +----+-----------+ 4 rows in set (0.00 sec)

When you want to select from both tables at once, there are a few differences in the syntax of the SELECT statement. First, you must ensure that all the tables you're using in your query appear in the FROM clause of the SELECT statement. Using the fruit and color example, if you simply want to select all columns and rows from both tables, you might think you would use the following SELECT statement:

mysql> SELECT * FROM fruit, color; +----+-----------+----+-----------+ | id | fruitname | id | colorname | +----+-----------+----+-----------+ |  1 | apple     |  1 | red       | |  2 | orange    |  1 | red       | |  3 | grape     |  1 | red       | |  4 | banana    |  1 | red       | |  1 | apple     |  2 | orange    | |  2 | orange    |  2 | orange    | |  3 | grape     |  2 | orange    | |  4 | banana    |  2 | orange    | |  1 | apple     |  3 | purple    | |  2 | orange    |  3 | purple    | |  3 | grape     |  3 | purple    | |  4 | banana    |  3 | purple    | |  1 | apple     |  4 | yellow    | |  2 | orange    |  4 | yellow    | |  3 | grape     |  4 | yellow    | |  4 | banana    |  4 | yellow    | +----+-----------+----+-----------+ 16 rows in set (0.00 sec)

Sixteen rows of repeated information are probably not what you were looking for! What this query did is literally join a row in the color table to each row in the fruit table. Because there are four records in the fruit table and four entries in the color table, that's 16 records returned to you.

When you select from multiple tables, you must build proper WHERE clauses to ensure that you really get what you want. In the case of the fruit and color tables, what you really want is to see the fruitname and colorname records from these two tables where the IDs of each match up. This brings us to the next nuance of the queryhow to indicate exactly which field you want when the fields are named the same in both tables!

Simply, you append the table name to the field name, like this:

tablename.fieldname

So, the query for selecting fruitname and colorname from both tables where the IDs match would be

mysql> SELECT fruitname, colorname FROM fruit, color WHERE fruit.id = color.id; +-----------+-----------+ | fruitname | colorname | +-----------+-----------+ | apple     | red       | | orange    | orange    | | grape     | purple    | | banana    | yellow    | +-----------+-----------+ 4 rows in set (0.00 sec)

However, if you attempt to select a column that appears in both tables with the same name, you will get an ambiguity error:

mysql> SELECT id, fruitname, colorname FROM fruit, color     -> WHERE fruit.id = color.id; ERROR 1052: Column: 'id' in field list is ambiguous

If you mean to select the ID from the fruit table, you would use

mysql> SELECT fruit.id, fruitname, colorname FROM fruit,     -> color WHERE fruit.id = color.id; +------+-----------+-----------+ | id   | fruitname | colorname | +------+-----------+-----------+ |    1 | apple     | red       | |    2 | orange    | orange    | |    3 | grape     | purple    | |    4 | banana    | yellow    | +------+-----------+-----------+ 4 rows in set (0.00 sec)

This was a basic example of joining two tables together for use in a single SELECT query. The JOIN keyword is an actual part of SQL, which enables you to build more complex queries.

Using JOIN

Several types of JOINs can be used in MySQL, all of which refer to the order in which the tables are put together and the results are displayed. The type of JOIN used with the fruit and color tables is called an INNER JOIN, although it wasn't written explicitly as such. To rewrite the SQL statement using the proper INNER JOIN syntax, you would use

mysql> SELECT fruitname, colorname FROM fruit     -> INNER JOIN color ON fruit.id = color.id; +-----------+-----------+ | fruitname | colorname | +-----------+-----------+ | apple     | red       | | orange    | orange    | | grape     | purple    | | banana    | yellow    | +-----------+-----------+ 4 rows in set (0.00 sec)

The ON clause replaces the WHERE clause you've seen before; in this instance it tells MySQL to join together the rows in the tables where the IDs match each other. When joining tables using ON clauses, you can use any conditions that you would use in a WHERE clause, including all the various logical and arithmetic operators.

Another common type of JOIN is the LEFT JOIN. When joining two tables with LEFT JOIN, all rows from the first table will be returned, no matter whether there are matches in the second table or not. Suppose you have two tables in an address book, one called master_name, containing basic records, and one called email, containing email records. Any records in the email table would be tied to a particular ID of a record in the master_name table. For example, take a look at these two tables:

mysql> SELECT name_id, firstname, lastname FROM master_name; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ | 1       | John      | Smith    | | 2       | Jane      | Smith    | | 3       | Jimbo     | Jones    | | 4       | Andy      | Smith    | | 5       | Chris     | Jones    | | 6       | Anna      | Bell     | | 7       | Jimmy     | Carr     | | 8       | Albert    | Smith    | | 9       | John      | Doe      | +---------+-----------+----------+ 9 rows in set (0.00 sec) mysql> select name_id, email from email; +---------+-------------------+ | name_id | email             | +---------+-------------------+ | 2       | [e-mail] | | 6       | [e-mail]  | | 9       | [e-mail]    | +---------+-------------------+ 3 rows in set (0.00 sec)

Using LEFT JOIN on these two tables, you can see that if a value from the email table doesn't exist, an empty value will appear in place of an email address:

mysql> SELECT firstname, lastname, email FROM master_name     -> LEFT JOIN email ON master_name.name_id = email.name_id; +-----------+----------+-------------------+ | firstname | lastname | email             | +-----------+----------+-------------------+ | John      | Smith    |                   | | Jane      | Smith    | [e-mail] | | Jimbo     | Jones    |                   | | Andy      | Smith    |                   | | Chris     | Jones    |                   | | Anna      | Bell     | [e-mail]  | | Jimmy     | Carr     |                   | | Albert    | Smith    |                   | | John      | Doe      | [e-mail]    | +-----------+----------+-------------------+ 9 rows in set (0.00 sec)

A RIGHT JOIN works like LEFT JOIN but with the table order reversed. In other words, when using a RIGHT JOIN, all rows from the second table will be returned, no matter whether there are matches in the first table or not. However, in the case of the master_name and email tables, there are only three rows in the email table, whereas there are nine rows in the master_name table. This means that only three of the nine rows will be returned:

mysql> SELECT firstname, lastname, email FROM master_name     -> RIGHT JOIN email ON master_name.name_id = email.name_id; +-----------+----------+-------------------+ | firstname | lastname | email             | +-----------+----------+-------------------+ | Jane      | Smith    | [e-mail] | | Anna      | Bell     | [e-mail]  | | John      | Doe      | [e-mail]    | +-----------+----------+-------------------+ 3 rows in set (0.00 sec)

Several different types of JOINs are available in MySQL, and you've learned about the most common types. To learn more about JOINs such as CROSS JOIN, STRAIGHT JOIN, and NATURAL JOIN, visit the MySQL Manual at http://dev.mysql.com/doc/refman/5.0/en/join.html.

Using Subqueries

Simply stated, a subquery is a SELECT statement that appears within another SQL statement. Such queries are useful because they often eliminate the need for bulky JOIN queries, and in the case of application programming, subqueries can eliminate the need for multiple queries within loops.

An example of the basic subquery syntax is shown here:

SELECT expressions_and_columns FROM table_name WHERE somecolumn = (SUBQUERY);

You can also use subqueries with UPDATE and DELETE statements, as shown here:

DELETE FROM table_name WHERE somecolumn = (SUBQUERY);

or

UPDATE table_name SET somecolumn = 'something' WHERE somecolumn = (SUBQUERY);

By the Way

The outer statement of a subquery can be SELECT, INSERT, UPDATE, DELETE, SET, or DO.

The subquery must always appear in parenthesesno exceptions!

When using subqueries, the WHERE portion of the outer statement does not have to use the = comparison operator. In addition to = you can use any of the basic comparison operators as well as keywords such as IN.

The following example uses a subquery to obtain records from users in the master_name table who have an email address in the email table:

mysql> SELECT firstname, lastname FROM master_name     -> WHERE name_id IN (SELECT name_id FROM email); +-----------+----------+ | firstname | lastname | +-----------+----------+ | Jane      | Smith    | | Anna      | Bell     | | John      | Doe      | +-----------+----------+ 3 rows in set (0.00 sec)

For a more detailed discussion of subqueries, including limitations, see the Subqueries section of the MySQL Manual at http://dev.mysql.com/doc/refman/5.0/en/subqueries.html.