UTEP Oracle 9i
UTEP Oracle 10g Notes

Join

Understanding the concept:

Join operations are probably the most common relational operations. Certainly they are fundamental to the model—it would not be feasible to decompose data into multiple relations were it not possible to recombine it as necessary. This is precisely what a join operator does; it combines recordsets based on the comparison of one or more common fields.

Joins are implemented using the JOIN clause of the SELECT statement. They are categorized based on the type of comparison between the fields involved and the way the results of the comparison are treated.

There are three type of join methods:

When the join comparison is made on the basis of equality, the join is an equi-join. In an equi-join operation, only those records that have matching values in the specified fields will be returned.

For example, OrderID is the primary key of the Orders table and a foreign key in the Order Details table.

After joined, it showed:

Technically, all joins are theta-joins, but by custom, if the comparison operator is equality, the join is always referred to as an equi-join or just as a join. A join based on any other comparison operator (<>, >, >=, <, <=) is a theta-join.

Theta-joins are extremely rare in practice, but they can be handy in solving certain kinds of problems. These problems mostly involve finding records that have a value greater than an average or total, or records that fall within a certain range.

For example, that you've created two views, one containing the average number of units sold for each product category and a second containing the total units sold by product.

After joined, the result showed:

Relational algebra also supports another kind of join, the outer join. An outer join returns all the records returned by an inner join, plus all the records from either or both of the other recordsets. The missing ("unmatched") values will be Null.

Outer joins are categorized as being left, right, or full, depending on which additional records are to be included.

Thus the following two statements both return all the records from X and only those records from Y where the <condition> evaluates to True:

SELECT * FROM X LEFT OUTER JOIN Y ON <condition>

SELECT * FROM Y RIGHT OUTER JOIN X ON <condition>