SQL Aliases
In this section, we will talk about Aliases, In and the
use of subqueries, and how these can be used in a 3-table example. First,
look at this query which prints the last name of those owners who have
placed an order and what the order is, only listing those orders which
can be filled (that is, there is a buyer who owns that ordered item):
SELECT OWN.OWNERLASTNAME Last Name, ORD.ITEMDESIRED Item Ordered
FROM ORDERS ORD, ANTIQUEOWNERS OWN
WHERE ORD.OWNERID = OWN.OWNERID
AND ORD.ITEMDESIRED IN
(SELECT ITEM
FROM ANTIQUES);
This gives:
| Last Name | Item Ordered |
| Smith | Table |
| Smith | Desk |
| Akins | Chair |
| Lawson | Mirror |
There are several things to note about this query:
- First, the "Last Name" and "Item Ordered" in the
Select lines gives the headers on the report.
- The OWN & ORD are aliases; these are new names for the two tables
listed in the FROM clause that are used as prefixes for all dot notations
of column names in the query (see above). This eliminates ambiguity, especially
in the equijoin WHERE clause where both tables have the column named OwnerID,
and the dot notation tells SQL that we are talking about two different
OwnerID's from the two different tables.
- Note that the Orders table is listed first in the FROM clause; this
makes sure listing is done off of that table, and the AntiqueOwners table
is only used for the detail information (Last Name).
