SQL Progressive (aliases), in, and subqueries

aliases, in, and subqueries

In this tutorial, we'll introduce the use of aliases, in, and subqueries. First we look at a query that searches for the LastName of all customers who have orders and what they order, as follows:

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);

The result of this query statement is:

Last Name Item Ordered

--------- ------------

Smith Table

Smith Desk

Akins Chair

Lawson Mirror

Here's a good explanation for the above statement:

"Last Name" and "Item Ordered" give the data header for the report.

OWN & Ord are aliases (aliases) that are used in the FROM clause and can be queried by adding a dot and a column name after them. This avoids ambiguity, especially when two columns are named Owenerid in the equijoin WHERE clause, and the dot tells SQL we use different ownerid of two different tables.

Notice here that the Orders table is listed first in the FROM clause, and that the Antiqueowners table is used for detailed information only (last Name). More importantly, the and force in subquery in the WHERE clause is invoked ("= any" or "= SOME" is equivalent to using in). But what did it do? When this subquery completes, it returns all items in the antiques table because there is no WHERE clause. Then, for rows listed from the Orders table, itemdesired must list an item in the list of items returned from the antiques table, and then the order can have another owner fill it out. You can think of this method: the subquery Returns a series of items from each itemdesired in the Orders table, and if the itemdesired is returned from the antiques table, then the condition is true.