Different types of join in PL/SQL

In order to understand about joins in PL/SQL it is worth of first create two tables and then try to understand about the different joins so that we have a ready made example which we can see to find out the actual meaning of joins in Pl/SQL.

Lets create two table of order supplier

Create table Orders

Create table Orders(OrderNumber varchar2(50),SupplierNumber varchar2(50));

insert into ORDERS values('123','1');
insert into ORDERS values('234','2');
insert into ORDERS values('345','3');
Insert into orders values('456','4');
Insert into orders values('789','7');

Create table Suppliers

Create table Suppliers(SupplierName varchar2(50),SupplierNumber varchar2(50));

insert into SUPPLIERS values('IBM','1');
insert into SUPPLIERS values('Oracle','2');
insert into SUPPLIERS values('Sun','3');
insert into SUPPLIERS values('BEA','4');
insert into SUPPLIERS values('Wipro','5');
Insert into Suppliers values('Adobe','6');

So the two tables are



The different type of joins in PL/SQL are

Inner join

In mathematical sense we can call it as A intersection B, i.e. choose the only component which are common in both the table.

Execute the following query to understand the behavior of inner join

select SUPPLIERS.SUPPLIERNAME,SUPPLIERS.SUPPLIERNUMBER,ORDERS.ORDERNUMBER
FROM SUPPLIERS
inner join ORDERS
ON SUPPLIERS.SupplierNumber=Orders.SupplierNumber;



You will get following result in supplier table the record with supplier number 7 has not come and in a similar way from the Orders table records with supplier number 5 and 6 has not come. This happened as this is an inner join which just allows the common data(intersection) to be displayed.



Left Outer join

In mathematical we can call it as A alone or (A-B)+(A intersection B). It will display all the records A and common components of A and B

Execute the following query to understand the behavior of Left Outer join

select SUPPLIERS.SUPPLIERNAME,SUPPLIERS.SUPPLIERNUMBER,ORDERS.ORDERNUMBER
from SUPPLIERS
LEFT OUTER join ORDERS
ON SUPPLIERS.SupplierNumber=Orders.SupplierNumber;

Now you can see the following result



As you can see it has displayed all the records from A even if Supplier number is not there.
and the corresponding Order number is null for the supplier which does not have a common supplier number


Right outer join

In mathematical we can call it as B alone or (B-A)+(A intersection B). It will display all the records of B and common components of A and B

Execute the following query to understand the behavior of Left Outer join

select SUPPLIERS.SUPPLIERNAME,SUPPLIERS.SUPPLIERNUMBER,ORDERS.ORDERNUMBER
from SUPPLIERS
RIGHT OUTER join ORDERS
ON SUPPLIERS.SupplierNumber=Orders.SupplierNumber;



Here you can see all the record for Orders table has come hence we have all the order number but the corresponding supplier name and number does not exist.

Full Outer join

In mathematical we can call it as A union B
It will display all contents of A and B and the repeated section in both A and B will be displayed only once.
This type of join returns all rows from table A and B with nulls in place where the join condition is not met.

Execute the following query to understand the behavior of Full Outer join

select SUPPLIERS.SUPPLIERNAME,SUPPLIERS.SUPPLIERNUMBER,ORDERS.ORDERNUMBER
from SUPPLIERS
FULL OUTER join ORDERS
ON SUPPLIERS.SupplierNumber=Orders.SupplierNumber;



You can observe that all the records are displayed and the columns which has no value is displayed as null.