SQL AS for Column Aliases

As your queries grow more sophisticated, the default column names in your results start to fall short. A calculated expression like price * stock_quantity gets a messy auto-generated label. A joined query returns two columns both named name, one from products and one from categories, and you cannot tell them apart. An abbreviated column name like qty makes perfect sense in the database schema but confuses everyone reading the report.

Column aliases solve all of these problems. The AS keyword lets you rename any column in your output to something clearer, more descriptive, or more user-friendly. The alias does not change anything in the database itself. It only affects how the column is labeled in the query results.

This guide covers everything you need to know about column aliases: basic renaming, aliasing expressions and calculations, handling aliases with spaces and special characters, where aliases can and cannot be used, and the common mistakes that trip up beginners. Every example uses the ShopSmart sample database with full outputs (we defined it in a previous guide here).

How Column Aliases Work

A column alias assigns a temporary name to a column or expression in your query results. You create one by placing the AS keyword after the column or expression, followed by the name you want:

SELECT column_name AS alias_name
FROM table_name;

The alias appears as the column header in your results instead of the original column name. Here is the simplest example:

-- Without alias
SELECT first_name, last_name
FROM customers;

Output:

-- With aliases
SELECT first_name AS first, last_name AS last
FROM customers;

Output:

The data is identical. Only the column headers changed. The first_name column is now displayed as "first" and last_name as "last."

Renaming Columns for Clarity

The most straightforward use of aliases is making column names more readable or more meaningful to the consumer of the data.

Making Technical Names User-Friendly

Database column names often use abbreviations, snake_case, or technical naming conventions that make sense to developers but not to business users reading a report:

SELECT first_name AS "First Name",
       last_name AS "Last Name",
       signup_date AS "Member Since"
FROM customers
ORDER BY signup_date;

Output:

The column signup_date becomes "Member Since," which is much more meaningful in a customer-facing report.

Disambiguating Columns from Joined Tables

When you join two tables that have columns with the same name, aliases prevent confusion:

-- Without aliases: Two columns named 'name', which is which?
SELECT p.name, c.name
FROM products p
JOIN categories c ON p.category_id = c.id;

Output:

Two columns both labeled "name" makes the results ambiguous. Aliases fix this immediately:

-- With aliases: Crystal clear
SELECT p.name AS product_name, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id;

Output:

Now there is zero ambiguity about what each column represents.

Shortening Long Column Names

Sometimes the original column name is unnecessarily verbose for the context:

SELECT name,
       stock_quantity AS stock,
       is_available AS available
FROM products
ORDER BY stock DESC;

Output:

The full column names stock_quantity and is_available are shortened to stock and available, keeping the output compact and scannable.

Using Aliases with Expressions

Aliases become essential when your SELECT list includes calculated expressions. Without an alias, the database auto-generates a column name based on the expression, and the result is often unreadable.

The Problem Without Aliases

-- No alias on the calculated column
SELECT name,
       price,
       stock_quantity,
       price * stock_quantity
FROM products
ORDER BY price * stock_quantity DESC
LIMIT 5;

Output (varies by database):

The calculated column gets a label like ?column? (PostgreSQL), price * stock_quantity (MySQL), or (No column name) (SQL Server). None of these are useful.

The Fix: Name Your Expressions

SELECT name,
       price,
       stock_quantity,
       price * stock_quantity AS inventory_value
FROM products
ORDER BY inventory_value DESC
LIMIT 5;

Output:

Now the calculated column has a clear, meaningful name: inventory_value.

Common Expression Aliases

Here are several examples of expressions that benefit from aliases:

-- Price with tax (8% tax rate)
SELECT name,
       price AS base_price,
       price * 0.08 AS tax_amount,
       price * 1.08 AS price_with_tax
FROM products
ORDER BY price_with_tax DESC
LIMIT 5;

Output:

-- Discount calculation
SELECT name,
       price AS original_price,
       price * 0.20 AS discount_amount,
       price * 0.80 AS sale_price
FROM products
WHERE price > 50
ORDER BY sale_price ASC;

Output:

Aliasing String Concatenation

Aliases work with string expressions too:

-- PostgreSQL / SQLite
SELECT first_name || ' ' || last_name AS full_name,
       email AS contact_email
FROM customers;

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       email AS contact_email
FROM customers;

Output:

Without the alias, the concatenation column would be labeled something like ?column? or CONCAT(first_name, ' ', last_name).

Aliasing Function Results

SELECT COUNT(*) AS total_products,
       MIN(price) AS cheapest_price,
       MAX(price) AS most_expensive,
       ROUND(AVG(price), 2) AS average_price
FROM products;

Output:

Without aliases, these columns would have auto-generated names like count, min, max, and round, which are vague and can even conflict with SQL keywords in some contexts.

Quoting Aliases with Spaces and Special Characters

By default, alias names must follow the same rules as column names: no spaces, no special characters, and they must start with a letter or underscore. But sometimes you need aliases with spaces or mixed case for presentation purposes.

Double Quotes for Special Aliases

Wrap the alias in double quotes to include spaces, special characters, or preserve specific casing:

SELECT name AS "Product Name",
       price AS "Unit Price ($)",
       stock_quantity AS "Units in Stock"
FROM products
ORDER BY price DESC
LIMIT 5;

Output:

The aliases now include spaces, parentheses, and a dollar sign, which makes them perfect for report headers.

Quoting Rules by Database

-- PostgreSQL / SQLite
SELECT name AS "Product Name" FROM products;

-- MySQL
SELECT name AS `Product Name` FROM products;

-- SQL Server
SELECT name AS [Product Name] FROM products;

The AS Keyword Is Optional (But You Should Use It)

In most SQL databases, the AS keyword is optional. You can create an alias by simply placing the new name after the column or expression:

-- With AS (explicit)
SELECT name AS product_name, price AS unit_price
FROM products;

-- Without AS (implicit)
SELECT name product_name, price unit_price
FROM products;

Both queries produce identical results:

Why You Should Always Use AS

Even though omitting AS works, it is strongly recommended to always include it. Here is why:

Readability: AS makes the alias relationship explicitly clear:

-- Clear: Obviously creating aliases
SELECT first_name AS first, last_name AS last FROM customers;

-- Confusing: Are these column names, aliases, or typos?
SELECT first_name first, last_name last FROM customers;

Prevents ambiguity: Without AS, it is easy to accidentally create unintended aliases, especially when you forget a comma:

-- Intended: Select two columns
SELECT name, price FROM products;

-- Bug: Missing comma, 'price' becomes an alias for 'name'
SELECT name price FROM products;

Output of the buggy query:

The query returns a single column of product names, but labeled as "price." This is the same missing-comma bug discussed in earlier guides, and it becomes even more confusing in longer queries.

Where Aliases Can and Cannot Be Used

This is a critical section. Aliases have specific rules about where they can be referenced within a query, and violating these rules is one of the most common beginner frustrations.

SQL Clause Execution Order

To understand alias limitations, you need to know the order in which SQL processes the clauses of a query. This is not the order you write them in:

The key insight: aliases are defined in the SELECT clause (step 5), so they do not exist yet when WHERE (step 2) is being evaluated.

Aliases Work in ORDER BY

ORDER BY is processed after SELECT, so it can reference aliases:

-- This works: ORDER BY can use the alias
SELECT name,
       price * stock_quantity AS inventory_value
FROM products
ORDER BY inventory_value DESC;

Output:

Aliases Do NOT Work in WHERE

WHERE is processed before SELECT, so aliases do not exist yet:

-- This FAILS: WHERE cannot see the alias
SELECT name,
       price * stock_quantity AS inventory_value
FROM products
WHERE inventory_value > 5000;
-- ERROR: column "inventory_value" does not exist

The fix: Repeat the full expression in the WHERE clause:

-- Correct: Use the full expression in WHERE
SELECT name,
       price * stock_quantity AS inventory_value
FROM products
WHERE price * stock_quantity > 5000
ORDER BY inventory_value DESC;

Output:

Yes, you must write price * stock_quantity twice. This feels redundant, but it is how SQL works due to the clause evaluation order.

MySQL Exception: Aliases in GROUP BY and HAVING

MySQL is more lenient than other databases and allows aliases in GROUP BY and HAVING clauses:

-- MySQL: This works
SELECT category_id AS cat, COUNT(*) AS product_count
FROM products
GROUP BY cat
HAVING product_count > 2;

-- PostgreSQL/SQL Server: This fails
-- You must use the original column name:
SELECT category_id AS cat, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;

For portability, always use the original column names or expressions in GROUP BY and HAVING.

Table Aliases

While this guide focuses on column aliases, it is worth noting that AS is also used to create table aliases, which shorten table names in queries, especially when joining:

-- Without table aliases: Verbose
SELECT products.name, categories.name
FROM products
JOIN categories ON products.category_id = categories.id;

-- With table aliases: Clean and concise
SELECT p.name AS product_name, c.name AS category_name
FROM products AS p
JOIN categories AS c ON p.category_id = c.id;

Output:

Table aliases (p for products, c for categories) let you reference columns with shorter prefixes throughout the query. Just like with column aliases, the AS keyword is optional for table aliases, and you will often see it omitted:

-- Both are equivalent
FROM products AS p
FROM products p

Practical Examples: Aliases in Real Scenarios

Product Catalog Display

SELECT p.name AS "Product",
       c.name AS "Category",
       p.price AS "Price",
       p.stock_quantity AS "In Stock",
       CASE
           WHEN p.is_available = true THEN 'Yes'
           ELSE 'No'
       END AS "Available"
FROM products AS p
JOIN categories AS c ON p.category_id = c.id
ORDER BY c.name, p.name;

Output:

This query produces a clean, report-ready result with human-friendly column names.

Order Summary Report

SELECT o.id AS "Order #",
       c.first_name || ' ' || c.last_name AS "Customer",
       o.order_date AS "Date",
       o.total_amount AS "Total",
       o.status AS "Status"
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
ORDER BY o.order_date DESC;

Output:

Pricing Analysis

SELECT name AS product,
       price AS current_price,
       ROUND(price * 0.85, 2) AS "15% Off",
       ROUND(price * 0.75, 2) AS "25% Off",
       ROUND(price * 0.50, 2) AS "50% Off"
FROM products
WHERE price > 40
ORDER BY current_price DESC;

Output:

Inventory Value Dashboard

SELECT name AS product,
       price AS unit_price,
       stock_quantity AS units,
       price * stock_quantity AS total_value,
       CASE
           WHEN stock_quantity = 0 THEN 'Out of Stock'
           WHEN stock_quantity < 30 THEN 'Low Stock'
           WHEN stock_quantity < 100 THEN 'Normal'
           ELSE 'Well Stocked'
       END AS stock_status
FROM products
ORDER BY total_value DESC;

Output:

This dashboard-style query uses aliases on every column, including calculated values and a CASE expression, to produce clean output that could be displayed directly in an admin panel.

Practical Exercises

Exercise 1

Display all products with their name labeled as "Product" and price labeled as "Cost."

SELECT name AS "Product",
       price AS "Cost"
FROM products
ORDER BY price;

Expected output:

Exercise 2

Calculate and display a 10% discount price for all products over $50, with clear aliases.

SELECT name AS product,
       price AS original_price,
       ROUND(price * 0.90, 2) AS discounted_price,
       ROUND(price * 0.10, 2) AS savings
FROM products
WHERE price > 50
ORDER BY savings DESC;

Expected output:

Exercise 3

Create a customer directory showing full name and city with user-friendly column headers.

SELECT first_name || ' ' || last_name AS "Full Name",
       COALESCE(city, 'Unknown') AS "City"
FROM customers
ORDER BY last_name;

Expected output:

Exercise 4

Show order details with customer names, using table and column aliases throughout.

SELECT o.id AS order_id,
       c.first_name || ' ' || c.last_name AS customer,
       o.total_amount AS total,
       o.status AS order_status
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
WHERE o.total_amount > 100
ORDER BY total DESC;

Expected output:

Exercise 5

Create an inventory report showing each product's name, stock status, and total inventory value.

SELECT name AS "Product",
       stock_quantity AS "Qty",
       price AS "Price",
       price * stock_quantity AS "Total Value",
       CASE
           WHEN stock_quantity = 0 THEN 'Reorder Now'
           WHEN stock_quantity < 50 THEN 'Running Low'
           ELSE 'In Stock'
       END AS "Status"
FROM products
ORDER BY "Total Value" DESC;

Expected output:

Key Takeaways

Column aliases are a simple but essential tool for producing clean, readable query results. Here is what you should remember:

• AS creates a temporary name for a column or expression in your query output
• Aliases do not modify the database. They only affect the current query's results
• Use aliases to rename columns for clarity: SELECT signup_date AS "Member Since"
• Use aliases to label expressions: SELECT price * 1.08 AS price_with_tax
• Use double quotes for aliases with spaces or special characters: AS "Product Name"
• Never use single quotes for aliases. Single quotes are for data values only
• The AS keyword is technically optional, but always include it for readability and to prevent bugs
• Aliases can be used in ORDER BY because it executes after SELECT
• Aliases cannot be used in WHERE because it executes before SELECT. Repeat the full expression instead
• Table aliases (FROM products AS p) shorten table references in complex queries with joins
• Well-chosen aliases turn raw query output into report-ready, presentation-quality results

Aliases are one of those features that seem minor at first but become indispensable as your queries grow in complexity. Every production query, every report, and every API response benefits from clear, well-named columns.