New Join Syntax

This new join syntax uses the new keywords inner join, left outer join, right outer join, and full outer join, instead of the (+) operator.

INNER Join:

SQL> select p.part_id, s.supplier_name
From part p inner join supplier s
On p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2

Remember, if we want to retain all the parts in the result set, irrespective of whether any supplier supplies them or not, then we need to perform an outer join. The corresponding outer join query using the new syntax will be:
OUTER JOIN
SQL> select p.part_id, s.supplier_name from part p left outer join supplier s on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
P4
P3
This is called a "left outer join" because all the rows from the table on the left (PART) are retained in the result set. If we want to retain all the suppliers in the result set, irrespective of whether they supply any part or not, then we need to perform a "right outer join". That would look like:
SQL> select p.part_id, s.supplier_name from part p right outer join supplier s on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
Supplier#3

However, the biggest advantage of the new join syntax is its support for full outer joins. Introduction of the ANSI standard join syntax in Oracle9i greatly simplifies the full outer join query. We are no longer limited by unidirectional outer join, and no longer need to use the UNION operation to perform the full outer join.
FULL OUTER JOIN
Oracle9i introduced the full outer join operation to carry out such operations, as in the following example:
SQL> select p.part_id, s.supplier_name
From part p full outer join supplier s
On p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
P4
P3
Supplier#3

The above SQL statement is not only smaller in size, it is much more elegant and intuitive as well. This ANSI join syntax is also more efficient than the UNION method of achieving a full outer join.

NATURAL Joins. A natural join, as its name implies, can be invoked when two or more tables share exactly the same columns needed for a successful equijoin. For example, these queries will return all Region and Country information for all countries whose name that contains the string "united":