Monday, November 5, 2007


We next focus on the use of aliases. There are two types of aliases that are used most frequently: column alias and table alias.

In short, column aliases exist to help organizing output. In the previous example, whenever we see total sales, it is listed as SUM(sales). While this is comprehensible, we can envision cases where the column heading can be complicated (especially if it involves several arithmetic operations). Using a column alias would greatly make the output much more readable.

The second type of alias is the table alias. This is accomplished by putting an alias directly after the table name in the FROM clause. This is convenient when you want to obtain information from two separate tables (the technical term is 'perform joins'). The advantage of using a table alias when doing joins is readily apparent when we talk about joins.

Before we get into joins, though, let's look at the syntax for both the column and table aliases:

SELECT "table_alias"."column_name1" "column_alias"
FROM "table_name" "table_alias"

Briefly, both types of aliases are placed directly after the item they alias for, separate by a white space. We again use our table, Store_Information,

Table Store_Information

store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

We use the same example as that in the SQL GROUP BY section, except that we have put in both the column alias and the table alias:

SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"
FROM Store_Information A1
GROUP BY A1.store_name


Total Sales
Los Angeles
San Diego
Notice that difference in the result: the column titles are now different. That is the result of using the column alias. Notice that instead of the somewhat cryptic "Sum(Sales)", we now have "Total Sales", which is much more understandable, as the column header. The advantage of using a table alias is not apparent in this example

No comments: