AS aliases for columns

Concepts covered: Query Basics

A result column takes its name from the table column it came from, and that name is often wrong for whoever reads the result. It may be too technical or too abbreviated, or the result may be headed for a tool that expects a different header. AS gives the result column a new name without touching the table. Write the column, then AS, then the name you want it to carry in the result.

SELECT
  product_name AS item,
  price AS unit_price
FROM products

products
product_id	product_name	category	price	in_stock
1	Desk Lamp	lighting	24	1
2	Standing Desk	furniture	349	1
3	Monitor Arm	accessories	89.5	0
4	Office Chair	furniture	199	1
5	USB Hub	accessories	18.75	1

Play it and watch the headers change while the values stay put: product_name becomes item and price becomes unit_price, and the 5 rows underneath hold the same values the table does. The alias lives in the result only. The products table still has a column called product_name, and the next query to read it has to call it product_name again, because an alias from 1 query is gone the moment that query finishes.

Names with spaces and capitals

An alias written as a bare word follows the same rules as a column name. It must start with a letter and may contain digits and the _ character after that. The database folds it to lower case, so AS Unit_Price comes back as unit_price. To give a result column a name with a space or a capital letter in it, wrap the alias in double quotes. The quotes make the name exactly what sits between them, spaces and capitals included, and any symbols as well, which is what a spreadsheet or a chart wants to display as a header.

SELECT
  product_name AS "Product",
  price AS "Unit Price (USD)"
FROM products

products
product_id	product_name	category	price	in_stock
1	Desk Lamp	lighting	24	1
2	Standing Desk	furniture	349	1
3	Monitor Arm	accessories	89.5	0
4	Office Chair	furniture	199	1
5	USB Hub	accessories	18.75	1

Result
Product	Unit Price (USD)
Desk Lamp	24
Standing Desk	349
Monitor Arm	89.5
Office Chair	199
USB Hub	18.75

Single quotes mean something different: 'Product' is a piece of text, a value, while "Product" is a name. Writing price AS 'Unit Price' fails with a syntax error because the database expects a name after AS and finds a value instead. Double quotes go around names, and single quotes go around text.

AS is optional, and still worth typing

The database accepts an alias with the keyword left out, so price unit_price means the same as price AS unit_price. The cost shows up when a comma goes missing: SELECT product_name price FROM products returns 1 column holding the product names under the header price, and raises no error at all, because the database read price as an alias for product_name. Typing AS every time makes the intent visible, and when a comma is lost the missing AS makes the mistake stand out on the page.

The same column under 2 names

SELECT
  product_name,
  price AS list_price,
  price AS compare_at_price
FROM products

products
product_id	product_name	category	price	in_stock
1	Desk Lamp	lighting	24	1
2	Standing Desk	furniture	349	1
3	Monitor Arm	accessories	89.5	0
4	Office Chair	furniture	199	1
5	USB Hub	accessories	18.75	1

Result
product_name	list_price	compare_at_price
Desk Lamp	24	24
Standing Desk	349	349
Monitor Arm	89.5	89.5
Office Chair	199	199
USB Hub	18.75	18.75

A column listed twice can carry a different alias each time, and the result then has 2 headers over identical values: list_price and compare_at_price both show 24.0 for the Desk Lamp. A storefront that wants a strike-through price next to the selling price can be fed this way before any discount exists. Aliases also carry meaning the raw name leaves out. A header of price says nothing about currency or quantity; unit_price_usd says both, and the person who opens the result next week will not have to ask.

Table column	Alias	What the alias adds
price	unit_price_usd	The quantity it refers to and the currency
in_stock	is_available	Reads as a yes or no answer
lead_days	delivery_days	The business meaning of the number
product_name	"Product"	A header fit for a spreadsheet

Which statement about this query is true?

SELECT
  category AS dept,
  price
FROM products

The products table's category column is renamed dept for every later query
The result has 2 columns, dept and price, and the table still has a column called category
The result has 3 columns, category, dept and price, since the alias adds a copy
The query fails because price has no alias and every column needs 1 when any column has 1

Adding an alias to a working query follows the same path every time. Find the item on the SELECT list whose header needs to change, type AS after it, then the new header. Use a bare lowercase name, with words joined by _, when the header has no spaces, and double quotes when it has spaces or capitals. A header wrapped in single quotes is a piece of text, so it has no place after AS.

About this lesson

This section is part of the Query Structure: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.