MySQL Cookbook, 2nd Edition

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 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 ...