Database Reference
In-Depth Information
3.8. Selecting Data from Multiple Tables
Problem
The answer to a question requires 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 must retrieve
information from multiple tables. Two types of statements 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 another, to perform a comparison between values selected by
the inner query against values selected by the outer query.
This recipe shows a couple brief examples to illustrate the basic ideas. Other examples
appear elsewhere: subqueries are used in various examples throughout the topic (for
example, Recipes
3.10
and
8.3
).
Chapter 14
discusses joins in detail, including some that
select from more than two tables.
The following examples use the
profile
table introduced in
Chapter 2
. Recall that it
lists the people on your buddy list:
mysql>
SELECT * FROM profile;
+----+---------+------------+-------+-----------------------+------+
| id | name | birth | color | foods | cats |
+----+---------+------------+-------+-----------------------+------+
| 1 | Sybil | 1970-04-13 | black | lutefisk,fadge,pizza | 0 |
| 2 | Nancy | 1969-09-30 | white | burrito,curry,eggroll | 3 |
| 3 | Ralph | 1973-11-02 | red | eggroll,pizza | 4 |
| 4 | Lothair | 1963-07-04 | blue | burrito,curry | 5 |
| 5 | Henry | 1965-02-14 | red | curry,fadge | 1 |
| 6 | Aaron | 1968-09-17 | green | lutefisk,fadge | 1 |
| 7 | Joanna | 1952-08-20 | green | lutefisk,fadge | 0 |
| 8 | Stephen | 1960-05-01 | white | burrito,pizza | 0 |
+----+---------+------------+-------+-----------------------+------+
Let's extend use of the
profile
table to include another table named
profile_con
tact
. This second table indicates how to contact people listed in the
profile
table via
various social media services and is defined like this:
CREATE
TABLE
profile_contact
(
profile_id
INT
UNSIGNED
NOT
NULL
,
#
ID
from
profile
table
service
VARCHAR
(
20
)
NOT
NULL
,
#
social
media
service
name
