Using multiple tables in a query

The SELECT statement syntax given at the end of the previous chapter shows that more than one table may be pointed in the FROM clause. A table listing that does not use WHERE clause is practically unused because this produces the relational operation of the Cartesian product of the tables involved. That is, each record in one table meshes with each record in another table. For example, the tables

SELECT *
FROM A, B;

produce the following result:

As a rule, the table listing is used with the condition of joining records from different tables in the WHERE clause. The condition for the above tables may be a coincidence of the values, say, in the columns a and c:

SELECT *
FROM A, B
WHERE a = c;

Now the result set of that query is the following table:

i.e. only those table rows that have equal values in specified columns (equi-join) are joined. Although some arbitrary conditions may be used, nevertheless the equi-join is most commonly used because this operation reproduces a certain entity split by two entities as a result of a normalization process.

Even though different tables have columns with the same names, the dot notation is needed for uniqueness:

<table_name>.<column_name>

In cases where ambiguity is not available, that notation need not be used.

Example 5.6.1

SELECT
DISTINCT PC.model, maker
FROM PC, Product
WHERE PC.model = Product.model
AND
price < 600;

As a result each model of the same maker occurs in the result set only once:

Sometimes the table in the FROM clause need to be pointed more than once. In this case renaming is indispensable.

Example 5.6.2

SELECT DISTINCT a.model AS model_1, b.model AS
model_2
FROM PC AS a, PC b
WHERE a.price = b.price AND
a.model
< b.model;

Here the condition A.model < B.model is to issue one of similar pairs that is only distinguished by rearrangement, for example: {1232, 1233} and {1233, 1232}. The DISTINCT keyword is only used to eliminate duplicate rows because equal models with the same price are in the PC table. As a result, we get the following table:

Renaming is also needed in case the FROMclause uses a subquery. So, the first example can be rewritten as follows:

SELECT DISTINCT
PC.model, maker
FROM PC, (SELECT maker, model
FROM Product
) AS Prod
WHERE pc.model = Prod.model AND
price <
600;

Note that in this case the Product qualifier may not be already used in other clauses of the SELECT statement. This is because the Product table is just out of use. Instead of this name the Prod alias is used. Moreover, references are only possible to those Product table columns listed in the subquery.

Behind a alias of derived table expression in brackets there can be a list of names of columns which will be used instead of columns names of table expression. The order of names should naturally be corresponding to the list of columns of table expression (in our case - to the list in SELECT clause). Thus we can avoid ambiguity of names and, as consequence, necessities of their specification. The previous example now can be rewritten as:

SELECT DISTINCT model, maker
FROM PC, (SELECT maker,
model
FROM Product
) AS Prod(maker, model_1)
WHERE model = model_1 AND
price < 600;

Suggested exercises: 16, 17, 34, 96, 108