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 and ). 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.
3.147.48.78