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 examples show 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 Specifying a Join in the WHERE Clause. 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 the table name.

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.