<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Joins in Oracle

Published on
11,820 Points
5,220 Views
1 Endorsement
Last Modified:
Approved
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:

Cartesian product
Equijoin
Non-equijoin
Outer join
Self join

SQL:1999 Compliant Joins:

Cross joins
Natural joins
Using Clause
Full or two sided outer joins
Arbitrary join conditions for outer 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;

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 ;

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 ;

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;

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  ;

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; 

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 ;

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) ;

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) ;

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) ;

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 ;

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 ;

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 ;

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);

Open in new window

1
Author:Swadhin Ray
0 Comments

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month