- 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
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:
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;