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.