Joins in Oracle

From the Oracle SQL Reference we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimpse into the different types of joins with examples.

Oracle proprietary Joins:


SQL:1999 Compliant Joins:



Cartesian Products:

A Cartesian product is formed when:

- A join condition is omitted or invalid

- All rows in the first table are joined to all rows in the second table

To avoid a Cartesian product always include a valid join condition in a WHERE clause.

Cartesian products are useful for some tests when we need to generate a large number of rows to simulate a reasonable amount of data

Example :

SELECT  last_name, department_name dept_name
FROM    employees, departments;

Select all Open in new window

Joining Tables:

Use a join to query data from more than one table

SELECT table1.column, table2.column
FROM   table1,table2
WHERE  table1.column1 = table2.column2 ;

Select all Open in new window

The join condition can be written in the WHERE clause
Prefix the column name with the table name when the same column name appears in more than one table
Joining  n tables need a minimum of n-1 join conditions

For the examples I have taken HR schema and tables like EMPLOYEES , DEPARTMENTS and JOB_GRADES tables.
Initially this schema will be locked when you install Oracle on your system, you can access these tables by unloking your 'HR' schema or user through
login with sys previlages.

Equi join:

•Equijoin is based on  equality sign, equals sign, or “=” condition
•Also called inner join or simple join

Example :

SELECT employees.employee_id,
       employees.department_id,
       departments.department_id,
       departments.location_id
FROM  employees, departments
WHERE employees.department_id = departments.department_id ;

Select all Open in new window

Use AND for additional search conditions

Table Aliases:

It simplify the queries
It also improve performance of the query
It provides a convenient to use "name" in place of the actual table name (needed for self joins - see below)

Example (using the alias "e" for employees and "d" for departments) :

SELECT e.employee_id, e.last_name, e.department_id, d.department_id,d.location_id
FROM   employees e, departments d
WHERE  e.department_id = d.department_id
AND    e.salary > 5000;

Select all Open in new window

Non-Equi join:

 The condition  other than an equality operator is know to be a non-equi join.

Example :

 

SELECT  last_name, salary, grade_level
FROM    employees, job_grades
WHERE   salary BETWEEN 5000  AND   10000  ;

Select all Open in new window

Outer Joins:

Use Outer join is used to return records which don’t  have direct match and Outer join operator is the plus sign (+).
In outer join operation , all records from the source table included in the result even though they don't satisfy the join condition.

Outer joins are classified into three types:
1) Left Outer Join
2) Right Outer Join
3) Full Outer Join

Syntax for Outer Joins:

  
SELECT   column names from both tables 
FROM     table name 1
LEFT|RIGHT|FULL OUTER JOIN table name 2 on condition; 

Select all Open in new window

The (+) sign must be kept on the side of the join that is deficient in information.
 
Example :
 

  
SELECT e.last_name, e.department_id,d.department_name
FROM   employees e,departments d
WHERE  e.department_id(+) = d.department_id ;

Select all Open in new window

Left Outer Join:

This type of outer join returns the unmatched rows from the left table which matched rows from the right table.
These selects all the matched rows of table 2 and unmatched rows from table 1.

SELECT  e.first_name,e.last_name,e.salary,d.department_id ,d.department_name
FROM    employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

Select all Open in new window

Right Outer Join:

Right outer join is also like as Left Outer Join. But a little differcence like it returns all the unmatched rows from the right table
plus matched rows from left table.
Using this we can use the outer join operator in the right side of the table.

SELECT  e.first_name,e.last_name,e.salary,d.department_id ,d.department_name
FROM    employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

Select all Open in new window

FULL Outer Join:

This is the third classification of the outer join. Full outer join is a combination of LEFT and RIGHT outer join.
In this the join operation combines both the result of left and right outer joins.
The join table will return the set of all the records from both tables and filling NULL values for the missing matches on either side.

SELECT  e.first_name,e.last_name,e.salary,d.department_id ,d.department_name
FROM    employees e
FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ;

Select all Open in new window

Self Joins:

When a table is joined to itself then it is called as Self join or in less words we can just say ” joining a table to itself  is called self  join” .
Example (and notice the use of table alias to provide a meaningful name for the data origin):

  
SELECT  worker.last_name|| ‘ works for ‘ || manager.last_name
FROM    employees worker, employees manager
WHERE   worker.manager_id = manager.employee_id ;

Select all Open in new window

Cross Join:

It forms a Cartessian Product whenever Join condition is not specified.
Like as the below example:

  
SELECT   first_name,department_name
FROM     employees
CROSS JOIN   departments ;

Select all Open in new window

Natural Joins:

These joins are equivalent to Equi Joins.
Joins with all the common columns present in the tables.
The column names as well as the data types should match.

  Example:

  
SELECT   employee_id,
         first_name,
         manager_id,
         department_name
FROM     employees
NATURAL JOIN departments ;

Select all Open in new window

USING Clause in Oracle:

No matter how many common columns are available in the tables, NATURAL JOIN will join with all the common columns
Use USING clause to join with specified columns

Example :

SELECT   employee_id,
         first_name,
         manager_id,
         department_name
FROM     employees
JOIN     departments 
USING (department,manager_id);

Select all Open in new window