You can have more than one table describing properties of the same set of entities. For example, the EMPLOYEE Table shows the number of the department to which an employee is assigned, and the DEPARTMENT Table shows which manager is assigned to each department number. To retrieve both sets of properties simultaneously, you can join the two tables on the matching columns, as shown in the following example. The value in WORKDEPT and DEPTNO represent the same entity and represent a join path between the DEPARTMENT and EMPLOYEE tables.
Figure 16. A Join Path between Two Tables
The DEPARTMENT table:
| DEPTNO | DEPTNAME | MGRNO | ADMRDEPT |
|---|---|---|---|
| D21 | Administration Support | 000070 | D01 |
The EMPLOYEE table:
| EMPNO | FIRSTNAME | LASTNAME | WORKDEPT | JOB |
|---|---|---|---|---|
| 000250 | Daniel | Smith | D21 | Clerk |
When you retrieve information about an entity from more than one table, make sure equal values represent the same entity. The connecting columns can have different names (like WORKDEPT and DEPTNO in the previous example), or they can have the same name (like the columns called DEPTNO in the department and project tables).