Recipe 3.13. Selecting Data from More Than One Table

Problem

You need to retrieve data from more than one table.

Solution

Use a join or a subquery.

Discussion

The queries shown so far select data from a single table, but sometimes you need to retrieve information from multiple tables. Two types of queries that accomplish this are joins and subqueries. A join matches rows in one table with rows in another and enables you to retrieve output rows that contain columns from either or both tables. A subquery is one query nested within the other. The result is a query that performs a comparison between values selected by the "inner" query against values selected by the "outer" query.

In this section, I will show a couple of brief examples to illustrate the basic ideas. Other examples appear elsewhere: subqueries are used in various examples throughout the book (for example, Recipes Section 3.16 and Section 3.17). Chapter 12 discusses joins in detail, including some that select from more than two tables.

The following examples use the profile table that was introduced in Chapter 2; recall that it lists the people on your buddy list. Let's extend the scenario that uses that table a little bit to include another table named profile_contact. This second table contains information about how to contact people listed in the profile table via various instant messaging systems and is defined like this:

CREATE TABLE profile_contact (   profile_id   INT UNSIGNED NOT NULL, # ID from profile table   service      CHAR(20) NOT NULL,     # messaging service name   contact_name CHAR(25) NOT NULL,     # name to use for contacting person   INDEX (profile_id) ); 

The table associates each row with the proper profile row via the profile_id column. The service and contact_name columns name the messaging service and the name to use for contacting the given person via that service. For the examples, assume that the table contains these rows:

mysql> SELECT * FROM profile_contact ORDER BY profile_id, service; +------------+---------+---------------+ | profile_id | service | contact_name  | +------------+---------+---------------+ |          1 | AIM     | user1-aimid   | |          1 | MSN     | user1-msnid   | |          2 | AIM     | user2-aimid   | |          2 | MSN     | user2-msnid   | |          2 | Yahoo   | user2-yahooid | |          4 | Yahoo   | user4-yahooid | +------------+---------+---------------+ 

A question that requires combining information from both tables is, "For each person in the profile table, show me the messaging services I can use to get in touch, and the contact name to use for each service." To answer this question, use a join. Select from both tables and match rows by comparing the id column from the profile table with the profile_id column from the profile_contact table:

mysql> SELECT id, name, service, contact_name     -> FROM profile INNER JOIN profile_contact ON id = profile_id; +----+------+---------+---------------+ | id | name | service | contact_name  | +----+------+---------+---------------+ |  1 | Fred | AIM     | user1-aimid   | |  1 | Fred | MSN     | user1-msnid   | |  2 | Mort | AIM     | user2-aimid   | |  2 | Mort | MSN     | user2-msnid   | |  2 | Mort | Yahoo   | user2-yahooid | |  4 | Carl | Yahoo   | user4-yahooid | +----+------+---------+---------------+ 

In the FROM clause, the query indicates the tables from which data should be selected, and the ON clause tells MySQL which columns to use when searching for matches between the two tables. In the resulting output, rows include the id and name columns from the profile table, and the service and contact_name columns from the profile_contact table.

Here's another question for which both tables are used to derive the answer: "List all the profile_contact records for Mort." To pull the proper rows from the profile_contact table, you need to know Mort's ID, which is stored in the profile table. To write the query without looking up Mort's ID yourself, use a subquery that, given his name, looks it up for you:

mysql> SELECT * FROM profile_contact     -> WHERE profile_id = (SELECT id FROM profile WHERE name = 'Mort'); +------------+---------+---------------+ | profile_id | service | contact_name  | +------------+---------+---------------+ |          2 | AIM     | user2-aimid   | |          2 | MSN     | user2-msnid   | |          2 | Yahoo   | user2-yahooid | +------------+---------+---------------+ 

Here the subquery appears as a nested SELECT statement enclosed within parentheses.