Oracle Certification - SQL Fundamentals I - Joining Tables

Types of Join

Equijoin / Inner Join

An equijoin uses the equality operator, and the join occurs where the columns specified have the same values.

Non-Equijoin

One table is associated with another by one or more rows, and a range determined by an inequality operator determines which rows are returned.

Self Join

A self join relates columns within the same table, and requires the table to be aliased and appear twice in the WHERE clause. Usually the columns will have a hierarchical relation with one another.

Outer Join

Rows with NULLs and non-matched values are excluded from equijoins and non-equijoins. Outer joins allow these orphaned row to be returned.

Cartesian Products

If two tables in a join have no join condition, then Oracle returns the Cartesian Product, combining each row of one table with each row of the other. So for example, if table A containing 3 rows forms a cartesian product with table B containing 4 rows the results will contain (3x4) 12 rows. This is rarely useful, and normally the result of a missing join condition.

Natural Joins

The Oracle syntax for a natural join uses the equals sign in the WHERE clause. For example to join the COUNTRIES and REGIONS tables on the REGION_ID column the syntax is:

Starting with Oracle 9i ANSI standard SQL can be used with Oracle, and this is necessary for the OCP exam. Natural joins can be created using three different keywords: NATURAL JOIN, USING and ON. By contrast these keywords are specified in the FROM clause.

NATURAL JOIN Clause

Using the NATURAL JOIN keyword Oracle will automatically join two tables using columns that share common names. So for example both the COUNTRIES and the REGIONS table have a REGION_ID column:

When using a NATURAL JOIN the tables cannot be qualified with table names or aliases. NATURAL JOIN lends itself to errors, where two columns unintentionally have the same name, and is not good practice.

JOIN USING Clause

The USING keyword allows the common column(s) in the two tables that should be joined, in this case the JOIN keyword associates the two tables:

Note: You cannot use table aliases with NATURAL JOIN or JOIN USING, this is a common exam question.

JOIN ON Clause

The ON keyword allows the columns that should be joined to be specified, be they identical or not. Again the JOIN keyword associates the two tables:

Nonequi Joins

The Oracle syntax for a nonequi join is again in the WHERE clause:

Using the more recent syntax with JOIN ON:

Nonequi joins are little used, and quite confusing so you can bet that there will be at least one in the exam. The >= and <= operators may also be used in place of BETWEEN.

Self Join

A self join is essentially joining a table to itself. So for example the standard Oracle format for a self join on the EMPLOYEES table is:

Using the ansi syntax with JOIN ON the same select statment looks like:

Outer Joins

In certain circumstances one of the tables joined in a select statement may not have a row for every row returned. In this case that table can be outer joined.

For example using Oracle syntax every column that may not return results, and will be outer joined has the (+) operator applied to it:

LEFT OUTER JOIN

A left outer join performs an inner join of table A and table B, table A is on the left of the JOIN keyword and any rows excluded because they do not match rows in table B are also returned. So re-writing the above example using this syntax:

Where common columns will be joined in both tables a NATURAL LEFT JOIN can be used:

RIGHT OUTER JOIN

A right outer join performs an inner join of table A and table B, table B is on the right of the JOIN keyword and any rows excluded because they do not match rows in table A are also returned. So re-writing the above example using this syntax just means reversing the order of the two tables:

FULL OUTER JOIN

A full outer join combines the results of a left and right outer join. Standard Oracle SQL would require a UNION statement to achieve this. So for example the following statement would return all matched employees and departments, along with departments that have no employees, and employees that are not assigned to a departments.

Note: The default behaviour for JOIN is an INNER join, to perform an outer join LEFT, RIGHT or FULL must be specified.

Cartesian Joins

A Cartesian product occurs when two tables are combined without a join condition. Oracle combines every row of table A with every row of table B. So for example if table A has 3 rows,and table B 4 the result set will contain 12 rows.

For example a cartesian product of DUAL and REGIONS:

CROSS JOIN

Using ANSI compliant syntax:

Summary

There are three mutually exclusive natural join clauses: NATURAL JOIN, JOIN USING and JOIN ON. NATURAL JOIN takes no conditions and joins identically named columns. JOIN USING takes a bracketed list of join columns, which are unqualified.

A LEFT OUTER JOIN allows rows in the table to the right of JOIN to be returned even if they do not match the join condition. A RIGHT OUTER JOIN allows rows from the table to the right of JOIN to be returned without matching the join condition. Finally a FULL OUTER JOIN combines the results of a LEFT and RIGHT outer join.

The next topic is Subqueries.