Joins in Oracle with Examples

Why Joins are required

The information needed to satisfy a user query requires more than one table.

For example, the EMPLOYEES table has a column with a department number but not a department name; the department name must be retrieved from the DEPARTMENTS table. You can get this information by joining the two tables on a common column, in this case, the DEPARTMENT_ID column. Two or more tables can also be joined in situations where the columns may not be equal.

Type of Joins

1) Equijoin or Inner Join or Natural Join

2) Outer Joins

                a) Left Outer Join

                b) Right Outer Join

                c) Full Outer Join

3) Self-Joins

Equijoin or Inner Join

A join between two tables where rows are returned if one or more columns in common between the two tables are equal and not NULL.

Outer Join

A join between two or more tables returning all the rows in one table whether or not the second table contains a match on the join condition

Self-Join

A join of a table to itself where a non-primary key column in the table is related to the primary key column of another row in the same table

Hierarchical 

A table design where one of the foreign keys in the table references the primary key of the same table in a parent-child relationship.

Cartesian Product 

A join between two tables where no join condition is specified, and as a result, every row in the first table is joined with every row in the second table.

Equijoin or Inner Join Syntax  

Company needs to see the department name in the report. That information is in the DEPARTMENTS table.

Now we will join the two tables on the common column,DEPARTMENT_ID, and produce a report that is much more readable:

Syntax

select    employee_id "Emp ID",

                last_name || ', ' || first_name "Name",

                department_name "Dept"

from      employees employees , departments departments

where    employees.department_id = departments .department_id;

Outer Join Syntax  oracle

select    e.employee_id 'Emp ID',

                e.last_name || ', ' || e.first_name 'Name',

                d.department_name 'Dept'

from      employees e left outer join departments d

                on e.department_id = d.department_id

where   e.job_id = 'SA_REP'; 

Full Outer Join Syntax  oracle

select    e.employee_id "Emp ID",

                e.last_name || ', ' || e.first_name "Name",

                d.department_name "Dept"

from      employees e full outer join departments d

                on e.department_id = d.department_id;

 

Self-Join Syntax  oracle

select    e.employee_id "Emp ID",

                e.last_name "Emp Name",

                m.employee_id "Mgr ID",

                m.last_name "Mgr Name"

from      employees e join employees m

                on e.manager_id = m.employee_id;