Lesson 11

In the next module, you will learn about joins. Joins let you bring information from more than one table together.
This allows you to present the data as one understandable result set.

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 us 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 | NO | PRI | NULL |
| fname | varchar(20) | NO | | NULL |
| lname | varchar(20) | NO | | NULL |
| start_date | date | NO | | NULL |
| 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 | Type | Null | Key | Default |
+---------+----------------------+------+-----+---------+
| dept_id | smallint(5) unsigned | No | PRI | NULL |
| name | varchar(20) | No | | NULL |
+---------+----------------------+------+-----+---------+
2 rows in set (0.03 sec)

Let us say you want to retrieve the first and last names of each employee along with the name of the department to which each employee is assigned. Your query will therefore need to retrieve the employee.fname, employee.lname, and department.name columns.

Question: How can you retrieve data from both tables in the same query?
The answer lies in the employee.dept_id column, which holds the ID of the department to which each employee is assigned (in more formal terms, the employee.dept_id column is the foreign key to the department table). The query, which you will see shortly, instructs the server to use the employee.dept_id column as the bridge between the employee and department tables, thereby allowing columns from both tables to be included in the result set of the query . This type of operation is known as a join.