Java Reference
In-Depth Information
for each use of a given field, so the
WHERE
clause tells the DBMS to return data from the various tables
where the Order_Number fields match up and equal 2. This gives the following ResultSet:
Order_number
Item_number
Qty
Name
Description
2
1001
2
Corn Flakes
Cereal
2
1004
1
Oatmeal
Cereal
2
1005
3
Chocolate Chip
Cookies
2
1010
6
Orange
Soda
Although this approach of prefixing the column name by the table name works well, it is rather verbose.
Conventionally, SQL queries are made using short aliases for the table names. The use of aliases is
discussed in the
next section
.
Using an alias for the table name in a query
Conventionally, SQL queries are made using short aliases for the table names. Frequently, the alias is
a single letter, as shown here:
SELECT o.Order_number, oi.Item_number, oi.Qty, i.Name,
i.Description
FROM Orders o, Ordered_Items oi, Inventory i
WHERE o.Order_number = oi.Order_number AND
i.Item_Number = oi.Item_Number AND o.Order_number = 2;
The alias is defined in the
FROM
clause, since that is where the tables are identified, and is used
throughout the rest of the query.
Caution
The most important aspect of using aliases succesfully is to understand the order in
which parts of a SQL statement are executed. The use of aliases is discussed in more
detail in
chapter 7
.
Figure 9-2
shows the results produced by executing this query using the Swing Database Query tool
built in
Chapter 7
.
