Types Of Joins In Oracle

We have the following types of joins in ORACLE

1     Equi join

2     Non-equi join

3     Self join

4     Natural join

5     Cross join

6     Outer join

           Left outer 

           Right outer 

           Full outer 

7     Inner join

8     Anti Join

9     Semi Join

And to use these joins we either can use ON clause or USING clause

Equi Join:

It is a join of Joining of two tables with an equality operator. Only matching rows of joined tables will be displayed.

   select *

   from emp e,

           dept d

   where e.deptno = d.deptno

   With ON clause:

   select *

   from emp e join dept d on (e.deptno = d.deptno )

   With using clause:

   select *

   from emp e join dept d using (e.deptno)

   ORA-01748: only simple column names allowed here

   If we use column alias with USING clause as then we will get the above error ORA-01748. To avoid the error          simply mention the column without using aliases as like below.

   select *

   from emp e join dept d using (deptno)

Non Equi Join:

   An nonequi (or theta) join is an join statement that uses an unequal operation (i.e: <>, >, <, !=, BETWEEN, etc.) to match rows from different tables.

   select * from

   emp e, salgrade sa

   where e.sal between sa.losal and sa.hisal

Self Join:

It is a join of a table to itself.

Scenario1: Display all employee records from EMP table who belongs to BLAKE department

To achieve the above, we can either use sub query or self join

SUB Query Statement:

select * from

emp e1

where e1.deptno in (select e2.deptno from emp e2 where ename = 'BLAKE')

Self Join Statement:

select * from

emp e1,

emp e2

where e1.deptno = e2.deptno

and e2.ename = 'BLAKE'

OR

select * from

emp e1,

emp e2

where e1.deptno = e2.deptno

and e1.ename = 'BLAKE'

Scenario2:

Consider the EMP table has the following data. Write an SQL to fetch employees whose salary is more than his manager. Manager name is BLAKE.

SUB Query Statement:

select * from

emp e1

where e1.sal > ( select e2.sal from emp e2 where e1.mgr = e2.empno and e2.ename = 'BLAKE')

Self Join Statement:

select * from

emp e1 join

emp e2 on ( e1.mgr = e2.empno )

and e1.sal > e2.sal

and e2.ename = 'BLAKE'

                                                            (OR)

select * from

emp e1, emp e2

where e1.mgr = e2.empno

and e1.sal > e2.sal

and e2.ename = 'BLAKE'

Natural Join:

A natural join is a join statement that compares the common columns of both tables with each other.

Limitations/Problems:

select * from

oeorder_header

natural join

oeorder_detail

This query fetches no records because oeorder_header is having primary key on order_no, order_code columns where as oeorder_detail is having primary key on order_no, order_code & line_no.

Cross Join:

The Cartesian product also referred to as a cross-join, returns all the rows in all the tables listed in the query.

select *

from   dba_objects

          cross join

          dba_views

The data fetches by cross join helpful for having test data.

Following SQL demonstrate test data generation                   

select ran_k ||'-'|| src from

(select 'A' ran_k from dual

union all

select '1' from dual

union all

select '2' from dual

union all

select '3' from dual

union all

select '4' from dual)

 cross join

(select 'X' src from dual

union all

select 'Y' from dual

union all

select 'Z' from dual

union all

select '2' from dual )

Output:

A-X

1-X

2-X

3-X

4-X

A-Y

1-Y

2-Y

3-Y

4-Y

A-Z

1-Z

2-Z

3-Z

4-Z

A-2

1-2

2-2

3-2

4-2

Inner Join:

An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

Two ways to join:

select *

from emp e inner join dept d

using (deptno)   -- Here deptno column displayed as a first column and it won’t repeat two times

select *

from emp e inner join dept d

on (e.deptno = d.deptno)  -- here deptno column will be displayed two times

Outer Join:

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Sample Tables:

Left Outer Join:

It displays all rows from first table used in from clause and matching rows from second table. For all the non matching rows in second table ORACLE returns null values.

select *

from table_one t1

left outer join

table_two t2

on (t1.col_one = t2.col_one)

OR

select *

from table_one t1,

table_two t2

where t1.col_one = t2.col_one (+)

Right Outer Join:

It displays all rows from second table used in from clause and matching rows from first table. For all the non matching rows in first table ORACLE returns null values.

select * from

table_one t1 right outer join table_two t2

on (t1.col_one = t2.col_one)

OR

select * from

table_one t1,

table_two t2

where t1.col_one (+) = t2.col_one

Full Outer Join:

It displays all matching rows from tables in FROM clause. For all the non matching rows in tables of FROM clause ORACLE returns null values.

select * from

table_one t1

full  join

table_two t2

on (t1.col_one = t2.col_one)

OR

select * from

table_one t1

full outer join

table_two t2

on (t1.col_one = t2.col_one)

Anti Join:

An anti join returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the sub query on the right side.


Semi Join:

A semi join returns rows that match an EXISTS sub query without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the sub query.