Apollo SQL > DDL & DML Statements

Aliases allow you to rename a physical column name of the result set to make it more clear what the column data represents. Aliases also allow you to differentiate between same-named fields of data from different tables within a join.

/* Creates a column header called Name for the combined

First and Last fields */

SELECT first + ' ' + last AS Name FROM test WHERE last = 'Smith'

/* Creates column headers called Thousands,

ShipDate, and SaleDate */

SELECT AmountPaid / 1000 AS Thousands, (AmountPaid - ItemsTotal),

ShipDate As SD, SaleDate FROM Orders

For example, the following query 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 As LastName, ORD.ITEMDESIRED As ItemOrdered

FROM ORDERS ORD, ANTIQUEOWNERS OWN

WHERE (ORD.OWNERID = OWN.OWNERID) AND ORD.ITEMDESIRED

IN (SELECT ITEM FROM ANTIQUES);

This results in:

LastName ItemOrdered

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

Smith Table

Smith Desk

Akins Chair

Lawson Mirror

There are several things to note about this query. First, the "LastName" and "ItemOrdered" in the Select lines specify the column headers to be used in the report.

OWN and ORD are aliases in this example. 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).

Most importantly, the AND in the WHERE clause forces the In Subquery to be invoked. 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.

Also notice, that in this case, that there happened to be an antique available for each one desired...obviously, that won't always be the case. In addition, notice that when the IN, "= ANY" is used, these keywords refer to any possible row matches -- not column matches. That is, you cannot put multiple columns in the subquery 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 a single column can be listed in the subquery, and the possible match comes from multiple row values in that one column, not vice-versa.

Aliases, AS, and IN

Aliases, AS, and IN