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 said to be joining the tables together.

Suppose you have two tables, fruit and color. You can select all rows from each of the two tables, 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 is probably not what you were going 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 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 query how 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.