Oracle Joins

Definition / Concept

If we need to retrieve data from more than one table we use JOINs. A join is a query, used to retrieve data from more than one table based on JOIN condition (Note: Same table can referenced more than once).

Conditions

In SQL terms, Join occurs when we specify more than one table in the FROM clause of the SELECT statement. Additionally, we also specify a condition in the WHERE clause to specify JOIN condition in the SELECT statement. Now, JOINs are conditions to join/compare two tables' data retrieved from the selected tables' columns based on JOIN Condition. Oracle combines the rows' data to compare with other tables' rows and evaulate the JOIN condition for TRUE/FALSE.

We need to qualify columns having same name with table names/aliases to avoid ambiguity errors, for joins can be extended to multiple tables. In case of multiple joins extending to more than 2 tables, let's say 3 for example, we create a recordset based on 1st join between 1st two tables & join the resulting recordset with 3rd table using 2nd Join.

Table 1 +
     |
   (JOIN)--->Recordset I +
     |                   |
Table 2 +                |
                       (JOIN)--- Final Recordset
                         |
                         |
                         +
                       Table 3

Now, we've understood what are joins, their need & application, let's see what are the different types of JOINs
available.

SIMPLE JOIN/INNER JOIN/EQUALITY JOIN/EQUIJOIN

All the above terms refer the same JOIN type. Let's call them INNER JOIN for our reference. Inner Join have equality operator as the JOIN condition. So, INNER JOIN returns only those rows from two tables that have same data for the specified columns.

Example:

+--------+              +---------+
|   ID   |              |   ID    |
|  NAME  |              | SALARY  |
|  AGE   |              |         |
|--------|              |---------|
|EMPLOYEE|              | PAYROLL |
+--------+              +---------+

We need to extract the salary of an employee from the payroll table. So, a INNER JOIN shall be created between EMPLOYEE & PAYROLL table, using ID columns from the two tables.

SELECT employee.id, employee.name, payroll.salary
FROM employee, payroll
WHERE employee.id = payroll.id;

SELF JOIN

If we compare the data of a table with itself, we create a self join. I'll give here a classical example of employees & managers. Suppose, we need to know the name of employees' manager from the employee table, we'll create a self join. For example purpose, I modify the EMPLOYEE table.

+--------+
|   ID   |
|  NAME  |
|  AGE   |
| MGR_ID |
|--------|
|EMPLOYEE|
+--------+

SELECT e1.name EMPLOYEE, e2.name MANAGER
FROM employee e1, employee e2
WHERE e1.mgr_id = e2.id;

CARTESIAN PRODUCT

If there is no comparison condition specified in a JOIN condition, a cartesian product is returned. In such a case, every row in a table is joined with every row in the other table of the JOIN. Let's say, if there are 3 rows in Ist table & 4 rows in the IInd table, 12 rows (3x4) would be produced.

+--------+              +---------+
|   ID   |              |   ID    |
|  NAME  |              | SALARY  |
|  AGE   |              |         |
|--------|              |---------|
|EMPLOYEE|              | PAYROLL |
+--------+              +---------+

SELECT employee.id, payroll.salary
FROM employee, payroll
WHERE employee.name LIKE 'A%';

NATURAL JOIN

In a NATURAL JOIN all columns with same names & datatype are joined from both tables. Here, we need to qualify the column name by table name or alias. All rows matching with the same column data are retrieved.

+--------+              +---------+
|   ID   |              |   ID    |
|  NAME  |              | SALARY  |
|  AGE   |              |         |
|--------|              |---------|
|EMPLOYEE|              | PAYROLL |
+--------+              +---------+

SELECT id, name, salary
FROM employee NATURAL JOIN payroll;

OUTER JOIN

Till now, all joins we've discussed retrieve all matching records from both tables. But sometimes, there is a situation when we also need to retrieve non-matching records from either one or both tables. For retrieving such non-matching records, we create OUTER JOINs. There are 3 type of OUTER JOINs based on from which side of the JOIN Condition we extract the non-matching records. We assume the following structure for our examples:

+--------+              +---------+
|   ID   |              |         |
|  NAME  |              |   ID    |
|  AGE   |              |  SALARY |
| MGR_ID |              |         |
|--------|              |---------|
|EMPLOYEE|              | PAYROLL |
+--------+              +---------+

RIGHT OUTER JOIN

As the name suggests, we extract non-matching records from the table mentioned on the right hand side of the JOIN condition. So, all the matching records from two tables, as well as non-matching records from the 2nd table (on right hand) are retrieved. NULL values are replaced for the non-existing values for columns extracted from table on the right hand side in non-matching rows.

SELECT e.id, p.salary
FROM employee e, payroll p
WHERE e.id = p.id (+);

LEFT OUTER JOIN

In this JOIN, we extract non-matching records from the table mentioned on the left hand side of the JOIN condition in addition to the matching records. NULL value is displayed for the columns extracted from table on the left hand side in the non-matching rows.

SELECT e.id, p.salary
FROM employee e, payroll p
WHERE e.id (+) = p.id;

FULL OUTER JOIN

In case of a full outer join, we extract matching records from both tables specified in JOIN condition, as well as non-matching records from both tables. Actually to create FULL OUTER JOIN, a UNION set is generated from both LEFT OUTER JOIN & RIGHT OUTER JOIN. NULL values are displayed for non-existing records of the other table in each non-matching row of either table.

SELECT e.id, p.salary
FROM employee e, payroll p
WHERE e.id = p.id (+)
UNION
SELECT e.id, p.salary
FROM employee e, payroll p
WHERE e.id (+) = p.id;

Note:

• SQL statements provided in the post were tested on Oracle 8 version only. Natural Joins don't exist on Oracle 8
• ANSI syntax has not been covered here.