Database Reference
In-Depth Information
Note
the use of the concatenation operator in the scalar expression (Sname || ' ' ||
Fname) and the introduction of the alias (
FullName
). Please be careful not to confuse
“alias” as used here, with the term described in Chapter 7. What we are talking about here
is a column alias.
12.7 Queries Involving Multiple Tables
Quite often, it will be required to pull data from multiple tables in order to service a
particular query. The situation may warrant a natural join, a theta join, an outer join, or a
Cartesian product. Oracle supports two different approaches to dealing with queries from
multiple tables — the traditional approach, and the newly introduced ANSI (American
National Standards Institute) approach.
12.7.1 The Traditional Method
The traditional method for treating queries involving multiple tables is to specify the join
condition in the
Where-Clause
. Let us take a few examples:
Example 17:
Referring to supplier-items database (of Chapters 3 and 4), suppose we
need to multiply quantity by item price to obtain the value of each shipped box. We will
display supplier-name and item-name also. The query is shown below:
Note:
This is an example of an equijoin. It also includes a scalar expression
(
SH.Qty * I.ItemPrice
) and a column alias (
Value
). Note the introduction of explicit
tuple variables
SH, S
and
I,
and the new column,
Value
. It is a good habit to introduce
tuple variables when the information queried is to come from multiple tables, and/or
comparisons are to be made in order to service the query. The result of the above query
would look like this:
Example 18:
Show all program-name and course-name combinations.
