«Back to Home

Oracle Jump Start
Topics

How To Use Joins In Oracle

Description
 
Joins are used to retrieve the data from the multiple tables in Oracle.
 
Types of join in Oracle are-
  • Inner join
  • Outer join
As we know, there are 3 types of Outer joins in Oracle, which are-
  • Left-Outer-Join
  • Right-Outer-Join
  • Full-Outer-Join
Inner Join

Inner join is a keyword in Oracle. Joins are used to combine the data from the tables.
 
Visual Illustration
 
In this diagram, Inner join represents the shaded area. Inner join returns the data, where both intersect.
9

Syntax
  1. Select columns  
  2. from table 1  
  3. inner join table 2  
  4. on table 1.column = table 2.column;  
Example
 
In the example, given below, there are two tables. Employee is the first table and Order1 is the second table.
  1. select Employee.emp_id,employee.emp_name  
  2. from employee  
  3. inner join order1  
  4. on employee.emp_id = order1.order_id;  
10

Left Outer Join
 
Left outer join returns all the rows from the left hand table. Outer join joins table A, table B and returns all the rows from table A and is called Left Outer Join in Oracle.
 
Visual Illustration
 
In this diagram, Left Outer Join represents the shaded area.
3 
 
Syntax
  1. Select columns  
  2. from table 1  
  3. Left Outer join table 2  
  4. ON table 1.column = table 2.column;  
Example
  1. Select Emp_id, Emp_name  
  2. from Employee  
  3. Left Outer Join order1  
  4. ON Employee.emp_id = Order1.Order_id  
4

Right Outer Join
 
Right Outer Join returns all the rows from the right hand table.
 
Visual Illustration
5 
 
Syntax
  1. Select columns  
  2. from table 1  
  3. Right outer join table 2  
  4. on table 1.column = table 2.column;  
Example
  1. Select Emp_id, Emp_name, Emp_job  
  2. from Employee  
  3. Right Outer Join order1  
  4. ON Employee.emp_id = Order1.Order_id;  
6

Full Outer Join
 
Full Outer Join returns all the rows from the left hand table and right hand with null value.
 
Visual Illustration
7 
Syntax
  1. Select columns  
  2. from table 1  
  3. Full outer join table 2  
  4. on table 1.column = table 2.column;  
Example
  1. Select Emp_id, Emp_name, Emp_job  
  2. from Employee  
  3. Full Outer Join order1  
  4. ON Employee.emp_id = Order1.Order_id;  
8

Summary
 
Thus, we learnt, Join is used to retrieve the data from more than one tables in a database. There are two types of Join in Oracle- Inner Join and Outer Join. We learnt, how to use these Joins with the examples.