Managing & Using MySQL, 2nd Edition

Joins

Joins put the “relational” in relational databases by enabling you to relate the data in one table with data in other tables. The basic form of a join is sometimes described as an inner join . Joining tables is a matter of specifying equality in columns from two tables:

SELECT book.title, author.name 
FROM author, book
WHERE book.author = author.id

This query pulls columns from two different tables where a relationship exists between rows in the two tables. Specifically, this query looks for situations in which the value of the author column in the book table matches the id value in the author table. Consider a database in which the book table looks like Table 3-3 and the author table looks like Table 3-4.

Table 3-3. A book table

ID	Title	Author	Pages
1	The Green Mile	4	894
2	Guards, Guards!	2	302
3	Imzadi	3	354
4	Gold	1	405
5	Howling Mad	3	294

Table 3-4. An author table

ID	Name	Citizen
1	Isaac Asimov	US
2	Terry Pratchett	UK
3	Peter David	US
4	Stephen King	US
5	Neil Gaiman	UK

An inner join creates a virtual table by combining the fields of both tables for rows that satisfy the query in both tables. In our example, the query specifies that the author field of the book table must be identical to the id field of the author table. The query’s result would look like Table 3-5.

Table 3-5. Query results based on an inner join

Book title	Author name
The Green Mile	Stephen King
Guards, Guards!	Terry Pratchet
Imzadi	Peter David
Gold ...