O'Reilly logo

Learning SQL by Alan Beaulieu

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

Chapter 5. Querying Multiple Tables

Because relational database design mandates that independent entities be placed in separate tables, you will need a mechanism for bringing multiple tables together in the same query. This mechanism is known as a join, and this chapter will concentrate on the simplest and most common join, the inner join; Chapter 10 will demonstrate all of the different join types.

What Is a Join?

Queries against a single table are certainly not rare, but you will find that most of your queries will require two, three, or even more tables. To illustrate, let's look at the definitions for the employee and department tables and then define a query that retrieves data from both tables:

    mysql> DESC employee;
    +--------------------+----------------------+------+-----+------------+
    | Field              | Type                 | Null | Key | Default    |
    +--------------------+----------------------+------+-----+------------+
    | emp_id             | smallint(5) unsigned |      | PRI | NULL       |
    | fname              | varchar(20)          |      |     |            |
    | lname              | varchar(20)          |      |     |            |
    | start_date         | date                 |      |     | 0000-00-00 |
    | end_date           | date                 | YES  |     | NULL       |
    | superior_emp_id    | smallint(5) unsigned | YES  | MUL | NULL       |
    | dept_id            | smallint(5) unsigned | YES  | MUL | NULL       |
    | title              | varchar(20)          | YES  |     | NULL       |
    | assigned_branch_id | smallint(5) unsigned | YES  | MUL | NULL       |
    +--------------------+----------------------+------+-----+------------+
    9 rows in set (0.11 sec)

    mysql> DESC department; +---------+----------------------+------+-----+---------+ | Field ...

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