Oracle Joins : ANSI Standard

Oracle Joins using ANSI Standard Syntax :

Have a look at data in part and supplier table before we start:

SQL> select * from part;

PART SUPP
---- ----
P1   S1
P2   S2
P3
P4

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

Inner Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p INNER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

OR

select p.part_id,supplier_id,s.supplier_name
from part p INNER JOIN supplier s
USING(supplier_id);

Note : We need not qualify join column like p.supplier_id/s.supplier_id
            when we utilize USING keyword.

part_id    suppid           supp_name
-----         -----         --------------------
P1            S1            Supplier#1
P2            S2            Supplier#2

Left Outer Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p LEFT OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p LEFT OUTER JOIN supplier s
USING(supplier_id)
;

Output :

part_id    SuppId           SuppName
----------    -----------    ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
P4
P3

Right Outer Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p RIGHT OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p RIGHT OUTER JOIN supplier s
USING(supplier_id)
;

Output :

part_id    SuppId           SuppName
----------    -----------   ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
                            Supplier#3


Full Outer Join

select p.part_id,p.supplier_id,s.supplier_name
from part p FULL OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p FULL OUTER JOIN supplier s
USING(supplier_id)
;

See the output

part_id    SuppId           SuppName
----------    -----------   ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
P3
P4
                            Supplier#3


Note : ANSI Standard is most effective than traditional way of joining the tables.

Oracle Joins : Traditional Syntax

Oracle Joins :

1. Traditional Syntax
2. ANSI standard since Oracle 9i

Types of Joins :
1. Self Join
2. Inner/Simple Join
3. Outer Join
     a. Left Outer
     b. Right Outer
     c. Full Outer

We use the below PART and SUPPLIER tables to understand the oracle joins in a lucid manner.

CREATE TABLE PART(
part_id         varchar2(5) constraint part_id_nn NOT NULL,
supplier_id     varchar2(5)
);

begin
insert into part values ('P1','S1');
insert into part values ('P2','S2');
insert into part values ('P3',' ');
insert into part values ('P4',' ');
commit;
end;
/

CREATE TABLE SUPPLIER(
supplier_id        varchar2(5) constraint supplierid_nn NOT NULL,
supplier_name    varchar2(20) constraint suppname_nn NOT NULL
);

begin
insert into supplier values ('S1','Supplier#1');
insert into supplier values ('S2','Supplier#2');
insert into supplier values ('S3','Supplier#3');
commit;
end;
/

Have a look at data in part and supplier table before we start:

SQL> select * from part;

PART SUPP
---- ----
P1   S1
P2   S2
P3
P4

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

Joins using traditional syntax:

Here we use operators '=' and '+' for joining tables.

Simple Join :

When a simple join is made on 2 tables, common data between the 2 tables are fetched.

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id;

part_id    suppid           supp_name
-----         -----         --------------------
P1            S1            Supplier#1
P2            S2            Supplier#2

Here we did not get the parts : P3 and P4 which doesn't have a supplier yet.
Also we did not get the Supplier#3 which doesn't have a part yet.

To fetch a full table data while joining we use 'Outer Join'

Left Outer Join :

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id(+)
;

See the result now, we can see the other 2 parts even though they have a supplier yet.
Here we have fetched entire rows from PART table.

part_id       SuppId         SuppName
----------    -----------    ----------------
P1            S1             Supplier#1
P2            S2             Supplier#2
P4
P3

Right Outer Join :

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id(+)=s.supplier_id
;

See the results now, We will fetch all suppliers even though they donot supply any Parts.

part_id    SuppId         SuppName
---------- -----------    ----------------
P1            S1          Supplier#1
P2            S2          Supplier#2
                          Supplier#3

Now how about this situation if we need all rows from PART and SUPPLIER Tables??

We go for FULL JOIN:

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id(+)
UNION
select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id(+)=s.supplier_id
;

See the output

part_id       SuppId         SuppName
----------    -----------    ----------------
P1            S1             Supplier#1
P2            S2             Supplier#2
P3
P4
                             Supplier#3


For ANSI Standard please go to



Oracle Joins : ANSI Standard

Thanks!