• #
  • 522552
  • #

Discover the world

Home Discover the world

USA

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.