Using Aliases for Columns

The SELECT statement is used to specify and retrieve columns from a table. Sometimes, the original column names in the database aren't ideal for a final report or analysis. They might be abbreviated (like prod_id), technical (like calculated_value_stream_1), or simply not descriptive enough for an audience. Additionally, when calculations are performed directly within a SELECT statement (which will be covered later), the database often assigns a generic or unhelpful name to the resulting calculated column.

This is where column aliases come in handy. An alias is a temporary, alternative name you assign to a column within the scope of a specific query. It doesn't change the actual column name in the database table; it only affects how the column header appears in the results of that particular SELECT statement. Using aliases significantly improves the readability and clarity of your query output.

Assigning Aliases with AS

The standard way to assign an alias to a column is by using the AS keyword followed by the desired alias name.

Let's imagine we have a Products table with columns named ProductID, ProductName, and UnitPrice. If we wanted to retrieve the product name and unit price but display them with more user-friendly headers like "Product Name" and "Price", we could write:

SELECT
  ProductName AS "Product Name",
  UnitPrice AS "Price"
FROM Products;

Explanation:

1. SELECT ProductName AS "Product Name": This selects the ProductName column but tells the database to display it with the header "Product Name" in the result set.
2. UnitPrice AS "Price": Similarly, this selects the UnitPrice column and assigns it the alias "Price".
3. FROM Products: Specifies the table we are querying.

Result:

Instead of seeing ProductName and UnitPrice as column headers, the output would look like this:

Product Name	Price
Laptop Pro	1200
Wireless Mouse	25
External Hard Drive	80
...	...

Using Quotes for Aliases:

Notice the use of double quotes (") around "Product Name". If your alias:

• Contains spaces (like "Product Name")
• Includes special characters (like "Price ($)")
• Is a reserved keyword in SQL (like SELECT or ORDER, although this is generally discouraged)
• Needs to preserve specific capitalization (some database systems are case-sensitive regarding aliases)

you must enclose the alias in double quotes ("). Some database systems might also use single quotes (') or square brackets ([]) for this purpose, but double quotes are the most common standard. If your alias is a single word without spaces or special characters (like Price), the quotes are usually optional. However, using quotes consistently, especially for multi-word aliases, is good practice for clarity and avoiding potential errors.

Implicit Aliasing (Optional AS)

Many SQL database systems also allow you to assign an alias simply by putting the desired alias name directly after the column name, separated by a space, without explicitly using the AS keyword.

Using the previous example:

SELECT
  ProductName "Product Name", -- AS is omitted here
  UnitPrice Price           -- AS is omitted here, quotes also optional for single word
FROM Products;

This query produces the exact same result as the one using AS. While this shorthand can save a bit of typing, explicitly using AS often makes the query easier to read and understand, especially for complex queries or when others need to review your code. For beginners, sticking with AS is generally recommended until you become very comfortable reading SQL.

Why Use Aliases?

• Readability: Provide clear, meaningful names for columns in your output, making reports and results easier to understand.
• Clarity with Calculations: When performing calculations (e.g., UnitPrice * Quantity), aliases give a name to the resulting computed value.
• Handling Duplicate Names: If you join tables (covered later) that have columns with the same name (e.g., an id column in multiple tables), aliases are essential to distinguish them in the result set.
• Conciseness: Shorten long or complex column names for easier reference within the query itself (especially useful with table aliases, discussed in the chapter on JOINs).

By renaming columns on the fly using aliases, you gain significant control over the presentation and usability of your query results without altering the underlying database structure.