You can have more than one table describing the attributes 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 attributes simultaneously, you can join the two tables on the matching columns, as shown in the following example. The values in WORKDEPT and DEPTNO represent the same entity, and represent a join path between the DEPARTMENT and EMPLOYEE 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, ensure that 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).