- Retrieving Columns with SELECT and FROM
- Creating Column Aliases with AS
- Eliminating Duplicate Rows with DISTINCT
- Sorting Rows with ORDER BY
- Filtering Rows with WHERE
- Combining and Negating Conditions with AND, OR, and NOT
- Matching Patterns with LIKE
- Range Filtering with BETWEEN
- List Filtering with IN
- Testing for Nulls with IS NULL
This chapter is from the book
This chapter is from the book
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;
Tips
- A column alias doesn’t change the name of a column in a table.
- To determine a column’s name in a table definition, see “Displaying Table Definitions” in Chapter 10.
- You can use a reserved keyword if you quote the alias. The query SELECT SUM(sales) AS "Sum" FROM titles; uses the reserved word SUM as a column alias, for example. For information about keywords, see “SQL Syntax” and “Identifiers” in Chapter 3.
- AS also is used to name derived columns (whose values are determined by expressions other than simple column names); see “Creating Derived Columns” in Chapter 5.
- You also can create table aliases with AS; see “Creating Table Aliases with AS” in Chapter 7.
Microsoft Access and PostgreSQL require the AS keyword for column references.Oracle and DB2 display unquoted column names and aliases in uppercase.
SQL*Plus (Oracle’s command-line processor) truncates column aliases to the number of characters specified in the table’s column definitions. The column alias "Postal code" displays as Posta in a CHAR(5) column, for example.
DBMSs have restrictions on embedded spaces, punctuation, and special characters in aliases; search your DBMS documentation for SELECT or AS.