Skip to Main Content
Learning SQL
book

Learning SQL

by Alan Beaulieu
August 2005
Beginner content levelBeginner
312 pages
8h 23m
English
O'Reilly Media, Inc.
Content preview from Learning SQL

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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL

Learning SQL

Guy Vaccaro
Learning SQL

Learning SQL

Alan Beaulieu
Learning SQL

Learning SQL

Ben Forta

Publisher Resources

ISBN: 0596007272Supplemental ContentErrata Page