Joins: ANSI/ISO SQL: 1999 standards

Starting from 9i, Oracle has introduced various join syntax that comply with ANSI/ISO SQL: 1999 standards.

CROSS JOIN
Cross Join, Cross Product or Cartesian Product represents a direct product of records in two or more tables. In other words it lists all rows from all the tables listed in the query. Each row in the first table is paired with all the rows in the second table. That is if the first table contains 20 rows and second table contains 60 rows, the output will consist of 20 * 60 = 1200 rows of data.

This is how normally cross product was represented in existing syntax before Oracle 9i:

select field_name
from first_table, second_table;

The ANSI standard of cross joining two tables is as follows:

select field_name
from first_table CROSS JOIN second_table;

NATURAL JOIN
A natural join performs a join for all columns with matching names in the tables present in the query list. This is the ANSI variant of giving join conditions for all matching fields explicitly.

select field_name
from first_table NATURAL JOIN second_table;

The normal way of achieving this would be like:

select field_name
from first_table a, second_table b
where a.field1 = b.field1
and a.field2 = b.field2;

The USING clause have to be used in case all the matching fields should not be equated. This is also ANSI variant.

select field_name
from first_table JOIN second_table USING (field1);

The normal way of achieving this would be like:

select field_name
from first_table a, second_table b
where a.field1 = b.field1;

The ON clause have to be used instead of USING if the field names in tables do not match. This is also the ANSI variant of SQL.

select field_name
from first_table a JOIN second_table b ON (a.field = b.field1);

The existing syntax is:

select field_name
from first_table a, second_table b
where a.field = b.field1;

Example of joining three tables with ANSI syntax:

select employee_id, city, department_name
from locations l
JOIN departments d ON (d.location_id = l.location_id)
JOIN employees e ON (d.department_id = e.department_id);

The existing syntax of joining the same would be like:

select employee_id, city, department_name
from locations l, departments d, employees e
where d.location_id = l.location_id
and d.department_id = e.department_id;

OUTER JOINS
There are three variants of outer joins available in Oracle. They being LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.

LEFT OUTER JOIN
The left outer join returns all rows from the first table. It joins the second table with first table and returns those rows also. For those rows in first table which does not have a match with second table NULLs will be returned.

select field_name
from first_table LEFT OUTER JOIN second_table ON (field=field1);

The existing syntax for getting the same result is:

select field_name
from first_table, second_table
where field=field1(+);

RIGHT OUTER JOIN
The right outer join is the revers of LEFT OUTER JOIN. That is it returns all rows from second table. It then joins the first table. For those rows that does not have a match in first table, NULLs will be returned.

select field_name
from first_table RIGHT OUTER JOIN second_table ON (field=field1);

The existing syntax for getting the same result is:

select field_name
from first_table, second_table
where field(+)=field1;

FULL OUTER JOIN
The full outer join fetches all rows from both first table and second table. It will fill in blanks for the non matching rows in each table.

select field_name
from first_table FULL OUTER JOIN second_table ON (field=field1);

There is no equivalent for full outer join in previous releases.