Multi-Table Queries

Multi-Table Queries
All the examples we have considered so far have a major limitation: the columns that are
to appear in the result table must all come from a single table. In many cases, this is
not sufficient. To combine columns from several tables into a result table we need to
use a join operation. The SQL join operation combines information from two tables
by forming pairs of related rows from the two tables. The row pairs that make up the
joined table are those where the matching columns in each of the two tables have the
same value.
If we need to obtain information from more than one table, the choice is between using
a subquery and using a join. If the final result table is to contain columns from different
tables, then we must use a join. To perform a join, we simply include more than one table
name in the FROM clause, using a comma as a separator, and typically including a
WHERE clause to specify the join column(s). It is also possible to use an alias for a table
named in the FROM clause. In this case, the alias is separated from the table name with
a space. An alias can be used to qualify a column name whenever there is ambiguity
regarding the source of the column name. It can also be used as a shorthand notation for
the table name. If an alias is provided it can be used anywhere in place of the table name.
Example 5.24 Simple join
List the names of all clients who have viewed a property along with any comment
supplied.
SELECT c.clientNo, fName, lName, propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;
We want to display the details from both the Client table and the Viewing table, and so we
have to use a join. The SELECT clause lists the columns to be displayed. Note that it is
necessary to qualify the client number, clientNo, in the SELECT list: clientNo could come
from either table, and we have to indicate which one. (We could equally well have chosen
the clientNo column from the Viewing table.) The qualification is achieved by prefixing the
column name with the appropriate table name (or its alias). In this case, we have used c as
the alias for the Client table.
To obtain the required rows, we include those rows from both tables that have identical
values in the clientNo columns, using the search condition (c.clientNo = v.clientNo). We call
these two columns the matching columns for the two tables. This is equivalent to the
relational algebra
The most common multi-table queries involve two tables that have a one-to-many (1:*)
(or a parent/child) relationship (see Section 11.6.2). The previous query involving clients
and viewings is an example of such a query. Each viewing (child) has an associated client
(parent), and each client (parent) can have many associated viewings (children). The pairs
of rows that generate the query results are parent/child row combinations. In Section 3.2.5
we described how primary key and foreign keys create the parent/child relationship in a
relational database: the table containing the primary key is the parent table and the table
containing the foreign key is the child table. To use the parent/child relationship in an
SQL query, we specify a search condition that compares the primary key and the foreign
key. In Example 5.24, we compared the primary key in the Client table, c.clientNo, with the
foreign key in the Viewing table, v.clientNo.
The SQL standard provides the following alternative ways to specify this join:
FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo
FROM Client JOIN Viewing USING clientNo
FROM Client NATURAL JOIN Viewing
In each case, the FROM clause replaces the original FROM and WHERE clauses.
However, the first alternative produces a table with two identical clientNo columns; the
remaining two produce a table with a single clientNo column.
Example 5.25 Sorting a join
For each branch office, list the numbers and names of staff who manage properties and
the properties that they manage.
SELECT s.branchNo, s.staffNo, fName, lName, propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;
To make the results more readable, we have ordered the output using the branch number
as the major sort key and the staff number and property number as the minor keys. The
result table is shown in Table 5.25.
Example 5.26 Three-table join
For each branch, list the numbers and names of staff who manage properties,
including the city in which the branch is located and the properties that the
staff manage.
SELECT b.branchNo, b.city, s.staffNo, fName, lName, propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;
The result table requires columns from three tables: Branch, Staff, and PropertyForRent, so a
join must be used. The Branch and Staff details are joined using the condition (b.branchNo
= s.branchNo), to link each branch to the staff who work there. The Staff and PropertyForRent
details are joined using the condition (s.staffNo = p.staffNo), to link staff to the properties
they manage. The result table is shown in Table 5.26.
Note, again, that the SQL standard provides alternative formulations for the FROM and
WHERE clauses, for example:
FROM (Branch b JOIN Staff s USING branchNo) AS bs
JOIN PropertyForRent p USING staffNo
Example 5.27 Multiple grouping columns
Find the number of properties handled by each staff member.
SELECT s.branchNo, s.staffNo, COUNT(*) AS myCount
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
To list the required numbers, we first need to find out which staff actually manage properties.
This can be found by joining the Staff and PropertyForRent tables on the staffNo column,
using the FROM/WHERE clauses. Next, we need to form groups consisting of the
branch number and staff number, using the GROUP BY clause. Finally, we sort the output
using the ORDER BY clause. The result table is shown in Table 5.27(a).