O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required