Joining Tables

By Paulus, 29 March, 2007

Joining tables brings together data from two or more tables in one query in order to get all the information you need. There are six different types of joins:

Element

Description

Cartesian Join

This join replicates each row from the first table with every row from the second table. This will display every possible combination and can be implemented by two methods:

  1. Not including a joining condition in the WHERE clause.
  2. Using the JOIN method with the CROSS JOIN keywords.

Equality Join

Through a commonly named and defined column a join can be crated. This also has two methods:

  1. Using the WHERE clause.
  2. Using the JOIN method with the NATURAL JOIN, JOIN….ON, JOIN….USING keywords.

Non-Equality Join

Joins tables when there are no equivalent rows.

  1. Using the WHERE clause.
  2. Using the JOIN method with the JOIN….ON keywords.

Self-Join

Joins a table to itself.

  1. Using the WHERE clause.
  2. Using the JOIN method with the JOIN….ON keywords.

Outer Join

Include records of a table in output when there is no matching record in the other table.

  1. Using the WHERE clause with a ( + ) operator.
  2. Using the JOIN method with the OUTER JOIN keywords with the assigned type of LEFT, RIGHT, FULL

Set Operators

Combines results of multiple SELECT statements. Includes the keywords UNION, UNION ALL, INTERSECT, and MINUS.

Cartesian Joins

Cartesian Joins are useful when performing certain statistical procedures. Each record from each table will be matched up to each record in the other tables. When selecting data from multiple tables omitting the JOIN also produces a Cartesian product. The traditional method of a Cartesian Join would be:

SELECT *
FROM Table1, Table2
ORDER BY R1, R2, R3;

Table1

Join Result

Table2

An undesired Cartesian Join would arise if you entered the query:

SELECT *
FROM Table1, Table2;

Starting in 10g, the CROSS keyword, combined with the JOIN keyword can be used in the FROM clause to explicitly instruct Oracle to create a Cartesian Join. When doing a cross join the commas are separated by the CROSS JOIN in the FROM clause:

SELECT *
FROM Table1 CROSS JOIN Table2;

Equality Joins

The most common join that will be used will require two tables or more tables. Each of these tables will have a common column. These joins are called equality joins but are also called equijoins, inner joins, or simple joins.

A common column is a column with equivalent data that exists in two or more tables. An Example would be a table called books and a table called publisher. They both have a column called pubid. A foreign key constraint allows the identification of a common column between two or more tables.

You can use an equal sign in the WHERE clause to create an equality join. When you do an equality join, you must use column qualifiers which are column names with the table name included. If this is not done Oracle will error out about ambiguity. An example of a column qualifier is:

Table1.id

The first part is the name of the table and the part after the period is the name of the column that is to be used.

SELECT col1, col2
FROM table1, table2
WHERE table1.col3 = tabl2.col3;

The NATUAL JOIN keywords automatically create a join between two tables based on common named fields. The USING clause allows you to create joins based on a column that has the same name and definition in both tables. When the tables that are to be joined in a USING clause do not have a commonly named and defined field, then you must add the ON clause to the JOIN keywords to tell Oracle how the tables are related.

In a NATUAL JOIN you can not use column qualifiers. This isn’t really wrong to specify qualifiers, but it doesn’t make much sense to do it in a NATUAL JOIN because it’s redundant. If column qualifiers are used in a NATURAL JOIN Oracle will return an error.

One downfall to using NATUAL JOINs is that if a column is later added to the table that already exists in another table that’s being queried, then Oracle will try to join the two tables based on that column. If you are unsure about whether or not a column is going to be added that don’t have any relationship at all. This can be gotten around if the JOIN and USING or ON clauses are used which can prevent any unexpected results due to table modification.

SELECT col1, col2
FROM table1 JOIN tabl2
USING(col3);

Or

SELECT col1, col2
FROM table1 JOIN table2
ON table1.col3 = table2.col3;

The differences between the USING and ON clauses are:

  1. The USING clause can only be used if both tables have a common column with the same name.
  2. A condition is specified in the ON clause which is not allowed in the USING clause.

Non-Equality Joins

Non-equality joins are joins that take two related common columns when no equivalent rows in the tables match. The non-equality join works pretty much same way as an equality join. The only difference in a non-equality join is that an equal sign is not used to establish a relationship in the ON clause.

SELECT col1, col2
FROM table1, table2
WHERE col3 BETWEEN col_val1 AND col_val2;

Self-Joins

Sometimes data in one column has a relationship with another column within the same table. The traditional method of a Self-Join is:

SELECT t1.col1, t1.col2, col_ref
FROM table1 t1, table2 t1
WHERE t1.col_ref = t1.col1;

Using the JOIN and ON clauses to make a self-join we would issue the following Query:

SELECT t1.col1, t1.col2, col_ref
FROM table1 t1 JOIN table2 t1
ON t1.col_ref = t1.col1;

Outer Joins

In all the other joins only rows with a corresponding record were returned. Those joins can be classified as INNER joins. The default INNER keyword can be included with the JOIN keyword. By using the OUTTER JOIN this instructs Oracle to include all records even if there is no data in the corresponding columns.

To tell Oracle to create NULL rows for records that do not have a matching record, a plus sign within parentheses is used ‘(+)’. This is placed in the joining condition of the WHERE clause immediately after the column name of the table that is missing the corresponding row. Two rules that must be remembered when working with the ‘(+)’:

  1. The outer join operator can be used for only one table in the joining condition.
  2. A condition that includes the outer join operator can not use the IN or the OR operator. If the IN or the OR keywords are used then it would imply that a row should be shown if it matches another row.

As mentioned above, using the ‘(+)’, you can only apply the join to one table. Using the JOIN keyword, you can create a FULL, RIGHT, or LEFT. The FULL OUTTER JOIN will include all rows from both tables. By default the use of the JOIN keyword implies an INNER JOIN.

The LEFT OUTTER JOIN means that if the table that is listed on the left side of the ON clause has an unmatched record. A NULL record will be displayed in the results. The way the RIGHT OUTTER JOIN is just the opposite. When using a FULL OUTTER JOIN will include NULL on either table.

Set Operators

Set operators are used to combine the results of two or more SELECT statements.

Set Operator

Description

UNION

Returns the results of combined SELECT statements with no duplicates.

UNION ALL

Returns the results of combined SELECT statements with duplicates.

INTERSECT

Returns the results of combined SELECT statements that only appear in both SELECTs.

MINUS

Removes the records of the second query from the first query that are found.

SELECT cols
FROM table
UNION | UNION ALL | INTERSECT | MINUS
SELECT cols
FROM table;