Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
This chapter is from the book

Creating Column Aliases with AS

In the query results so far, I’ve allowed the DBMS to use default values for column headings. (A column’s default heading in a result is the source column’s name in the table definition.) You can use the AS clause to create a column alias. A column alias is an alternative name (identifier) that you specify to control how column headings are displayed in a result. Use column aliases if column names are cryptic, hard to type, too long, or too short.

A column alias immediately follows a column name in the SELECT clause of a SELECT statement. Enclose the alias in single or double quotes if it’s a reserved keyword or if it contains spaces, punctuation, or special characters. You can omit the quotes if the alias is a single non-reserved word that contains only letters, digits, or underscores. If you want a particular column to retain its default heading, omit its AS clause.

To create column aliases:

  • Type:

    SELECT column1 [AS] alias1,
           column2 [AS] alias2,
           ...
           columnN [AS] aliasN
      FROM table;

    column1, column2, ..., columnN are column names; alias1, alias2, ..., aliasN are their corresponding column aliases; and table is the name of the table that contains column1, column2, ....

Listing 4.5 shows the syntactic variations of the AS clause. Figure 4.5 shows the result of Listing 4.5.

Listing 4.5. The AS clause specifies a column alias to display in results. This statement shows alternative constructions for AS syntax. In your programs, pick one construction and use it consistently. See Figure 4.5 for the result.

SELECT au_fname AS "First name",
       au_lname AS 'Last name',
       city AS City,
       state,
       zip 'Postal code'
  FROM authors;

Figure 4.5 Result of Listing 4.5.

First name Last name City          state Postal code
----------- ----------- ------------- ----- -------------
Sarah       Buchman     Bronx         NY    10468
Wendy       Heydemark   Boulder       CO    80303
Hallie      Hull        San Francisco CA    94123
Klee        Hull        San Francisco CA    94123
Christian   Kells       New York      NY    10014
            Kellsey     Palo Alto     CA    94305
Paddy       O'Furniture Sarasota      FL    34236

In standard SQL and most DBMSs, the keyword AS is optional, but you should always include it and surround aliases with double quotes to make your SQL code more portable and readable. With these syntactic conventions, Listing 4.5 is equivalent to:

SELECT au_fname AS "First name",
       au_lname AS "Last name",
       city     AS "City",
       state,
       zip      AS "Postal code"
  FROM authors;
  • + Share This
  • 🔖 Save To Your Account