Computing a join

Computing a join
A join is a subset of a more general combination of two tables known as the Cartesian
product (see Section 4.1.2). The Cartesian product of two tables is another table consisting
of all possible pairs of rows from the two tables. The columns of the product table are
all the columns of the first table followed by all the columns of the second table. If we
specify a two-table query without a WHERE clause, SQL produces the Cartesian product
of the two tables as the query result. In fact, the ISO standard provides a special form of
the SELECT statement for the Cartesian product:
SELECT [DISTINCT | ALL] {* | columnList}
FROM TableName1 CROSS JOIN TableName2
Consider again Example 5.24, where we joined the Client and Viewing tables using the
matching column, clientNo. Using the data from Figure 3.3, the Cartesian product of these
two tables would contain 20 rows (4 clients * 5 viewings = 20 rows). It is equivalent to the
query used in Example 5.24 without the WHERE clause.
Conceptually, the procedure for generating the results of a SELECT with a join is as follows:
(1) Form the Cartesian product of the tables named in the FROM clause.
(2) If there is a WHERE clause, apply the search condition to each row of the product
table, retaining those rows that satisfy the condition. In terms of the relational algebra,
this operation yields a restriction of the Cartesian product.
(3) For each remaining row, determine the value of each item in the SELECT list to produce
a single row in the result table.
(4) If SELECT DISTINCT has been specified, eliminate any duplicate rows from the
result table. In the relational algebra, Steps 3 and 4 are equivalent to a projection of
the restriction over the columns mentioned in the SELECT list.
(5) If there is an ORDER BY clause, sort the result table as required.
Outer joins
The join operation combines data from two tables by forming pairs of related rows
where the matching columns in each table have the same value. If one row of a table is
unmatched, the row is omitted from the result table. This has been the case for the joins
we examined above. The ISO standard provides another set of join operators called outer
joins (see Section 4.1.3). The Outer join retains rows that do not satisfy the join condition.
To understand the Outer join operators, consider the following two simplified Branch and
PropertyForRent tables, which we refer to as Branch1 and PropertyForRent1, respectively:
The (Inner) join of these two tables:
SELECT b.*, p.*
FROM Branch1 b, PropertyForRent1 p
WHERE b.bCity = p.pCity;
produces the result table shown in Table 5.27(b).
The result table has two rows where the cities are the same. In particular, note that there
is no row corresponding to the branch office in Bristol and there is no row corresponding
to the property in Aberdeen. If we want to include the unmatched rows in the result table,
we can use an Outer join. There are three types of Outer join: Left, Right, and Full Outer
joins. We illustrate their functionality in the following examples.
Example 5.28 Left Outer join
List all branch offices and any properties that are in the same city.
The Left Outer join of these two tables:
SELECT b.*, p.*
FROM Branch1 b LEFT JOIN PropertyForRent1 p ON b.bCity = p.pCity;
produces the result table shown in Table 5.28. In this example the Left Outer join includes
not only those rows that have the same city, but also those rows of the first (left) table that
are unmatched with rows from the second (right) table. The columns from the second table
are filled with NULLs.
Example 5.29 Right Outer join
List all properties and any branch offices that are in the same city.
The Right Outer join of these two tables:
SELECT b.*, p.*
FROM Branch1 b RIGHT JOIN PropertyForRent1 p ON b.bCity = p.pCity;
produces the result table shown in Table 5.29. In this example the Right Outer join
includes not only those rows that have the same city, but also those rows of the second
(right) table that are unmatched with rows from the first (left) table. The columns from the
first table are filled with NULLs.
Example 5.30 Full Outer join
List the branch offices and properties that are in the same city along with any
unmatched branches or properties.
The Full Outer join of these two tables:
SELECT b.*, p.*
FROM Branch1 b FULL JOIN PropertyForRent1 p ON b.bCity = p.pCity;
produces the result table shown in Table 5.30. In this case, the Full Outer join includes not
only those rows that have the same city, but also those rows that are unmatched in both
tables. The unmatched columns are filled with NULLs.