Aliases for Tables or Views

You can declare an alias for a table or view in the FROM clause. If you do so, you must use the alias to refer to the table or view in other clauses of the SELECT statement. You can also use aliases to make the query shorter.

The following example shows typical uses of the FROM clause. The first query selects all the columns and rows from the customer table. The second query uses a join between the customer and orders table to select all the customers who have placed orders.

SELECT * FROM customer
SELECT fname, lname, order_num FROM customer, orders
   WHERE customer.customer_num = orders.customer_num

The next example is equivalent to the second query in the preceding example, but it declares aliases in the FROM clause and uses them in the WHERE clause:

SELECT fname, lname, order_num FROM customer c, orders o
   WHERE c.customer_num = o.customer_num

Aliases (sometimes called correlation names) are especially useful with a self-join. For more information about self-joins, see Self-Joins. In a self-join, you must list the table name twice in the FROM clause and declare a different alias for each of the two instances of table name.

The AS Keyword

If you use a potentially ambiguous word as an alias (or as a display label), you must begin its declaration with the keyword AS. This keyword is required if you use any of the keywords ORDER, FOR, AT, GROUP, HAVING, INTO, NOT, UNION, WHERE, WITH, CREATE, or GRANT as an alias for a table or view.

The database server would issue an error if the next example did not include the AS keyword to indicate that not is a display label, rather than an operator:

CREATE TABLE t1(a INT);
SELECT a AS not FROM t1

If you do not declare an alias for a collection-derived table, the database server assigns an implementation-dependent name to it.