Using Table Aliases

In Chapter 10, "Creating Calculated Fields," you learned how to use aliases to refer to retrieved table columns. The syntax to alias a column looks like this:

• Input

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS
vend_title
FROM vendors
ORDER BY vend_name;

In addition to using aliases for column names and calculated fields, SQL also enables you to alias table names. There are two primary reasons to do this:

• To shorten the SQL syntax

• To enable multiple uses of the same table within a single SELECT statement

Take a look at the following SELECT statement. It is basically the same statement as an example used in the previous chapter, but it has been modified to use aliases:

• Input

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'TNT2';

• Analysis

You'll notice that the three tables in the FROM clauses all have aliases. customers AS c establishes c as an alias for customers, and so on. This enables you to use the abbreviated c instead of the full text customers. In this example, the table aliases were used only in the WHERE clause, but aliases are not limited to just WHERE. You can use aliases in the SELECT list, the ORDER BY clause, and in any other part of the statement as well.

It is also worth noting that table aliases are only used during query execution. Unlike column aliases, table aliases are never returned to the client.