Section 2.5 Aliases & In/Subqueries
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:
1. First, the "Last Name" and "Item Ordered" in the Select lines gives the headers on the report.
2. 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 OwnerIDs from the two different tables.
3. 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).
4. Most importantly, the AND in the WHERE clause forces the In Subquery to be invoked ("=
ANY" or "= SOME" are two equivalent uses of IN). What this does is, the subquery is
performed, returning all of the Items owned from the Antiques table, as there is no WHERE
clause. Then, for a row from the Orders table to be listed, the ItemDesired must be in that
returned list of Items owned from the Antiques table, thus listing an item only if the order can
be filled from another owner. You can think of it this way: the subquery returns a set of Items
from which each ItemDesired in the Orders table is compared; the In condition is true only if
the ItemDesired is in that returned set from the Antiques table.
5. Also notice, that in this case, that there happened to be an antique available for each one
desired...obviously, that wont always be the case. In addition, notice that when the IN, "=
ANY", or "= SOME" is used, that these keywords refer to any possible row matches, not
column matches...that is, you cannot put multiple columns in the sub query Select clause, in an
attempt to match the column in the outer Where clause to one of multiple possible column
values in the subquery; only one column can be listed in the sub query, and the possible match
comes from multiple row values in that one column, not vice-versa.
Whew! Thats enough on the topic of complex SELECT queries for now. Now on to other SQL
statements.