Joining Multiple Tables

Joining Multiple Tables

A three-way join is a join of three tables. You can join as many tables as needed to retrieve information. For example, you might want to find employees, their dependents' names, and the department names for those employees. This requires accessing three tables:- EMPLOYEES, DEPENDENTS, and DEPARTMENTS.

Syntax

In the FROM clause, you identify the tables you want to join.

FROM table1         
JOIN table2 ON conditon_x
JOIN table3 ON condition_y

Example

SELECT e.last_name, d.first_name, w.department_name
FROM   employees e
JOIN   dependents d
ON     d.relative_id = e.employee_id
JOIN   departments w
ON     w.department_id = e.department_id

Self-Joining Tables

The ON clause can also be used to join columns that have different names (in the same table or in a different table). For example, you can perform a self-join of the EMPLOYEES table based on the EMPLOYEE_ID and MANAGER_ID columns.