Oracle: Creating Joins with the ON Clause

How to create a join with the ON clause in Oracle?

• The join condition for the natural join is basically an equijoin of identical column names.
• ON clause can be used to join columns that have different names.
• Use the ON clause to specify conditions or specify columns to join.
• The join condition is separated from other search conditions.
• This is the most easiest and widely used form of the join clauses.

Syntax:

SELECT table1.column, table2.column
FROM table1
JOIN table2 ON (table1.column_name = table2.column_name);

Explanation:

• table1, table2 are the name of the tables participating in joining.
• The natural join syntax contains the NATURAL keyword, the JOIN…ON syntax does not.
• An error occurs if the NATURAL and ON keywords occur in the same join clause.
• The JOIN…ON clause allows one or more equijoin columns to specify in brackets after the ON keyword.
• The equijoin columns are fully qualified as table1.column_name = table2.column_name (optionally specified in brackets) after the ON keyword.

Example: Creating Joins with the ON clause in Oracle

In this example, the employee_id column in the emplolyees table and department_id in departments table are joined using the ON clause. Wherever a employee_id in the EMPLOYEES table equals a department ID in the DEPARTMENTS table, the row is returned. The table alias is necessary to qualify the matching column_names.

Sample table: employees

Sample table: department

SQL Code:

SQL> SELECT e.employee_id, e.last_name, e.department_id,
2  d.department_id, d.location_id
3  from departments d
4  join employees e on (e.employee_id=d.department_id);

Sample Output:

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        100 King                                 90           100        1700
        110 Chen                                100           110        1700
        120 Weiss                                50           120        1700
        130 Atkinson                             50           130        1700
        140 Patel                                50           140        1700
        150 Tucker                               80           150        1700
        160 Doran                                80           160        1700
        170 Fox                                  80           170        1700
        180 Taylor                               50           180        1700
        190 Gates                                50           190        1700
        200 Whalen                               10           200        1700


11 rows selected.

Using Table Aliases with the USING Clause

Example: Creating Joins with the ON clause and additional conditions

You can use the WHERE clause or the AND clause to apply additional conditions. The following example is identical with the previous example except with an additional condition of manager ID equal to 149.

SQL Code:

SQL> SELECT e.employee_id, e.last_name,
2  d.department_id, d.location_id
3  from departments d
4  join employees e 
5  on (e.employee_id=d.department_id)
6  AND e.manager_id = 108;

Sample Output:

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- -----------
        110 Chen                                110        1700
 

Oracle: Creating Three-Way Joins with the ON Clause

A three-way join is created with three tables. It is an SQL:1999–compliant syntax where joins are performed from left to right. In the following example :

• The first join to be performed is EMPLOYEES JOIN DEPARTMENTS.
• The first join condition can reference columns in EMPLOYEES and DEPARTMENTS but cannot reference columns in the third table i.e. LOCATIONS.
• The second join condition can reference columns from all three tables.

Sample table: employees

Sample table : department

Sample table: locations

SQL Code:

SQL> SELECT e.last_name,e.employee_id, l.city, d.department_name
2  FROM employees e
3  JOIN departments d
4  USING (department_id)
5  JOIN locations l
6  USING (location_id);

Sample Output:

LAST_NAME                 EMPLOYEE_ID CITY                  DEPARTMENT_ID
NAME
------------------------- ----------- -------------------- -----------
King                              100 Seattle               Executive
Kochhar                           101 Seattle               Executive
De Haan                           102 Seattle               Executive
Hunold                            103 Southlake             IT
Ernst                             104 Southlake             IT
Austin                            105 Southlake             IT
Pataballa                         106 Southlake             IT
Lorentz                           107 Southlake             IT
Greenberg                         108 Seattle               Finance
Faviet                            109 Seattle               Finance
Chen                              110 Seattle               Finance
Sciarra                           111 Seattle               Finance
Urman                             112 Seattle               Finance
Popp                              113 Seattle               Finance
....
 

Outputs of the said SQL statement shown here is taken by using Oracle Database 11g Express Edition.

Previous: NATURAL JOINS
Next: JOINS with USING Clause