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 ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.