Different types of Joins in Oracle

Sometimes it is necessary to work with multiple tables as though they were a single entity.Then a single SQL sentence can manipulate data from all the tables. Joins are used to achieve this.

Join is a method used to combine two or more tables, views or materialized views based on a common condition.

Different types of Joins

1. CROSS JOIN/CARTESIAN PRODUCT
2. NATURAL JOIN
3. [INNER] JOIN ... USING
4. [INNER] JOIN ... ON
5. Multiple Joins
6. {LEFT | RIGHT | FULL} [OUTER] JOIN
1. LEFT OUTER JOIN
2. RIGHT JOIN
3. FULL OUTER JOIN
7. SELF JOIN

CROSS JOIN

The CROSS JOIN produces a Cartesian product. It is the equivalent of not specifying a join criteria.This join lists all the rows in all tables.

SELECT e.employee_name,
       d.department_name
FROM   employees e
       CROSS JOIN departments d;

SELECT e.employee_name,
       d.department_name
FROM   employees e, departments d;

NATURAL JOIN

The NATURAL JOIN performs a join for all columns with matching names in the two tables.

SELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d;

SELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d
WHERE  d.department_id = 20

[INNER] JOIN ... USING

The USING clause is used if several columns share the same name, but you do not wish to join using all of these common columns. The columns listed in the USING clause cannot have any qualifiers in the statement, including the WHERE clause.

SELECT e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id);

SELECT e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id)
WHERE  d.department_id = 20;

SELECT d.department_name,
       e.employee_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

[INNER] JOIN ... ON

The ON clause is used to join tables where the column names do not match. The join conditions are removed from the filter conditions in the where clause.

SELECT d.department_name,
       e.employee_name
FROM   employees e
       JOIN departments d ON e.department_id = d.department_id;

SELECT d.department_name,
       e.employee_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

Multiple Joins

Multiple Joins are those where more than two tables are joined. The SQL: 1999 standard assumes the tables are joined from the left to the right, with the join conditions only being able to reference columns relating to the current join and any previous joins to the left.

SELECT employee_id,
       city,
       department_name
FROM   locations l
       JOIN departments d ON (d.location_id = l.location_id)
       JOIN employees e ON (d.department_id = e.department_id);

SELECT employee_id,
       city,
       department_name
FROM   locations l,
       departments d,
       employees e
WHERE  d.location_id   = l.location_id
AND    d.department_id = e.department_id;

{LEFT | RIGHT | FULL} [OUTER] JOIN

There are three variations on the outer join.

1. Left Outer Join :
   The LEFT [OUTER] JOIN returns all the rows from the table on the left side of the join, along with the values from the right hand side, or NULLs if a matching row doesn't exist.

   ANSI/ISO Syntax	Existing Syntax/Theta Style
   SELECT d.department_name, e.employee_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id;	SELECT d.department_name, e.employee_name FROM departments d, employees e WHERE d.department_id = e.department_id(+); Table order is irrelevant for non-ANSI syntax. It is placement of "(+)" that counts.

2. Right Outer Join :
   The RIGHT [OUTER] JOIN returns all the rows from the table on the right side of the join, along with the values from the left hand side, or NULLs if a matching row doesn't exist.

   ANSI/ISO Syntax	Existing Syntax/Theta Style
   SELECT d.department_name, e.employee_name FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;	SELECT d.department_name, e.employee_name FROM departments d, employees e WHERE d.department_id = e.department_id(+); Table order is irrelevant for non-ANSI syntax. It is placement of "(+)" that counts.

3. Full Outer Join :
   The FULL [OUTER] JOIN returns all rows from both tables, filling in any blanks with nulls.

   ANSI/ISO Syntax	Existing Syntax/Theta Style
   SELECT d.department_name, e.employee_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;	SELECT d.department_name, e.employee_name FROM employees e, departments d WHERE e.department_id = d.department_id (+) UNION ALL SELECT d.department_name, e.employee_name FROM departments d, employees e WHERE d.department_id = e.department_id (+) AND e.employee_name IS NULL; No equivalent as single join. Must use UNION or UNION ALL of two outer joins.

SELF JOIN

In some situations, it is necessary to join a table to itself, as though joining two separate tables. This is referred to as a SELF JOIN.

SELECT e.employee_name "Employee",
       d.fname "Manager"
FROM   employees e, 
       employees d 
WHERE e.mngr_no=d.empno;

Read Oracle Anti-Joins and Semi-Joins