You want to write a query that uses information from more than one table.
Use a join—that is, a query that refers to multiple tables and that tells MySQL how to match up information from them.
The essential idea behind a join is that it combines rows in one
table with rows in one or more other tables. A
full join between tables produces all
possible combinations of rows. For example, joining a 100-row table
to a 200-row table produces a result containing 100 X 200,
or 20,000 rows. With larger tables, or joins between more than two
tables, a result set can easily become immense—possibly causing
the MySQL server to run out of temporary table space. Because of
that, and because you rarely want all the combinations anyway, a join
normally includes a
that narrows the focus of the query. This section introduces basic
join syntax, and later sections show how joins help you answer
specific types of questions.
Suppose that you’re a very unimaginative dresser, and you have trouble picking out your wardrobe each day. So you decide to let MySQL help you. First, enter your shirts into one table and your ties into another:
CREATE TABLE shirt (item CHAR(20));mysql>
INSERT INTO shirt (item)->
CREATE TABLE tie (item CHAR(20));mysql>
INSERT INTO tie (item)->
VALUES('Fleur de lis'),('Paisley'),('Polka Dot');
You can list what’s in each table by using separate ...