Aliases with AS

Rename columns and tables in query results using aliases.

10 min read
Beginner

Introduction to Aliases

Have you ever run a query and gotten results with confusing column names like COUNT(*), price * quantity, or CONCAT(first_name, ' ', last_name)?

Or maybe you're joining multiple tables and need to distinguish between columns with the same name?

Aliases solve these problems! They let you give temporary, readable names to columns and tables in your query results.

A temporary name assigned to a column or table for the duration of a query. Makes results more readable and simplifies complex queries. Created using the AS keyword (though AS is optional for column aliases).

Why use aliases?

  • Make column names more readable
  • Give meaningful names to calculated columns
  • Shorten long table names
  • Distinguish between columns from different tables (in joins)
  • Required in some cases (GROUP BY with calculations)

Let's work with our employees table:

employees
id
first_name
last_name
department
salary
hire_date
1AliceSmithEngineering950002020-03-15
2BobJohnsonMarketing750002021-07-22
3CharlieDavisEngineering850002019-11-03
4DianaWilsonSales700002022-01-10

Column Aliases with AS

Use AS to give a column a more readable name in the results.

Syntax:

SELECT column_name AS alias_name
FROM table_name;
sql
-- Rename columns for clarity
SELECT
  first_name AS given_name,
  last_name AS family_name,
  salary AS annual_salary
FROM employees;

The column names in the result are now more descriptive! The actual database columns remain unchanged - aliases only affect the query output.

AS is optional for column aliases:

sql
-- These are identical (AS is optional)
SELECT first_name AS name FROM employees;
SELECT first_name name FROM employees;

Best Practice: Always use AS for column aliases! Even though it's optional, it makes your queries clearer and easier to read.

Aliases for Calculated Columns

When you calculate values, aliases make the results much more readable:

sql
-- Without alias (confusing column name)
SELECT salary * 12
FROM employees;
sql
-- With alias (clear column name)
SELECT salary * 12 AS annual_salary
FROM employees;

Much better! Now readers know what the number represents.

Multiple calculations:

sql
SELECT
  first_name,
  last_name,
  salary,
  salary * 12 AS annual_salary,
  salary * 12 * 0.30 AS annual_taxes_estimate,
  salary * 12 * 0.70 AS annual_take_home
FROM employees;

Aliases for String Concatenation

Combine columns with aliases for clean output:

sql
SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  department,
  salary
FROM employees;

Without the alias, the column name would be CONCAT(first_name, ' ', last_name) - not user-friendly!

Aliases for Aggregate Functions

Aggregate functions always need aliases for readable output:

sql
-- Without aliases (ugly column names)
SELECT
  COUNT(*),
  AVG(salary),
  MIN(salary),
  MAX(salary)
FROM employees;
sql
-- With aliases (clear column names)
SELECT
  COUNT(*) AS total_employees,
  ROUND(AVG(salary), 2) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary
FROM employees;

With GROUP BY:

sql
SELECT
  department,
  COUNT(*) AS employee_count,
  ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;

Aliases with Spaces and Special Characters

If you need spaces or special characters in an alias, use quotes:

sql
SELECT
  first_name AS "First Name",
  salary AS "Annual Salary ($)",
  department AS "Department Name"
FROM employees;

Database differences:

  • MySQL: Use backticks: Annual Salary
  • PostgreSQL/Oracle/Standard SQL: Use double quotes: "Annual Salary"
  • SQL Server: Use square brackets: [Annual Salary]

For maximum compatibility, avoid spaces in aliases!

Table Aliases

You can also give tables shorter names using aliases. This is essential when working with joins (next lessons).

Syntax:

SELECT column
FROM table_name AS alias;
sql
-- Give employees table a short alias 'e'
SELECT e.first_name, e.last_name, e.department
FROM employees AS e;

We used e as an alias for employees, then referenced columns as e.first_name, e.last_name, etc.

AS is optional for table aliases too:

sql
-- These are identical
SELECT e.first_name FROM employees AS e;
SELECT e.first_name FROM employees e;

Why use table aliases?

  • Shorter to type (especially with long table names)
  • Required when joining a table to itself
  • Clarifies which table a column comes from (in joins)
sql
-- Long table name
SELECT emp.first_name, emp.salary
FROM employee_information_master emp
WHERE emp.department = 'Engineering';

Table Aliases with Joins (Preview)

Table aliases become essential when joining multiple tables. Here's a preview (you'll learn joins in detail soon):

orders
id
customer_id
order_date
total
11012024-01-15250.00
21022024-01-20150.00
customers
id
name
city
101Alice CorpNew York
102Bob IncLondon
sql
-- Using table aliases to clarify which 'id' we mean
SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.order_date,
  o.total
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id;

Both tables have an id column! Without aliases (o.id vs c.id), SQL wouldn't know which one you mean.

Using Aliases in WHERE, ORDER BY, and GROUP BY

Important rule: You generally can't use column aliases in WHERE clauses because WHERE is evaluated before the SELECT list.

sql
-- ❌ This FAILS in most databases
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 1000000;
sql
-- ✓ This WORKS - repeat the expression
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 1000000;

You CAN use aliases in ORDER BY:

sql
SELECT
  first_name,
  salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;  -- ✓ Alias works here!

GROUP BY: In some databases (MySQL) you can use aliases, but for portability, repeat the expression:

sql
-- Best practice: repeat the expression
SELECT
  YEAR(hire_date) AS hire_year,
  COUNT(*) AS employees_hired
FROM employees
GROUP BY YEAR(hire_date)  -- Repeat expression for compatibility
ORDER BY hire_year;

Common Mistakes to Avoid

Don't make these mistakes:

  1. Using alias in WHERE: ❌

    • Wrong: SELECT salary * 12 AS annual WHERE annual > 100000
    • Right: SELECT salary * 12 AS annual ... WHERE salary * 12 > 100000
    • WHERE runs before SELECT, so it doesn't know about aliases

  2. Forgetting quotes for spaces: ❌

    • Wrong: SELECT name AS First Name
    • Right: SELECT name AS "First Name"
    • Or better: Avoid spaces! AS first_name

  3. Confusing table and column aliases: ❌

    • Table alias: FROM employees e
    • Column alias: SELECT salary AS sal
    • These are different!

  4. Using reserved words as aliases without quotes: ❌

    • Avoid: AS select, AS from, AS where
    • These are SQL keywords!

  5. Reusing same alias: ❌

    • SELECT salary AS total, bonus AS total
    • Each alias should be unique

  6. Not using table aliases in joins: Confusing! ❌

    • Makes queries hard to read when columns come from multiple tables

  7. Inconsistent alias style: Not an error, but messy

    • Pick a style and stick with it: employee_count vs employeeCount vs EMPLOYEECOUNT

Practical Examples

Example 1: Sales report with calculations

products
id
name
price
units_sold
cost
1Laptop99950600
2Mouse2920015
3Keyboard7915040
sql
SELECT
  name AS product_name,
  price,
  units_sold,
  price * units_sold AS total_revenue,
  cost * units_sold AS total_cost,
  (price - cost) * units_sold AS total_profit,
  ROUND((price - cost) / price * 100, 2) AS profit_margin_pct
FROM products
ORDER BY total_profit DESC;

Example 2: Employee directory

sql
SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  department AS dept,
  CONCAT('$', FORMAT(salary, 0)) AS formatted_salary,
  YEAR(CURRENT_DATE) - YEAR(hire_date) AS years_employed
FROM employees
ORDER BY years_employed DESC;

Example 3: Product categories (GROUP BY with aliases)

sql
SELECT
  category,
  COUNT(*) AS product_count,
  ROUND(AVG(price), 2) AS avg_price,
  SUM(stock) AS total_stock
FROM products p  -- Table alias (even though we don't use it)
GROUP BY category
ORDER BY product_count DESC;

Key Takeaways

What you learned:

✅ Aliases give temporary names to columns and tables ✅ Use AS keyword (optional but recommended for columns) ✅ Column aliases make results more readable ✅ Table aliases shorten table names and are essential for joins ✅ Aliases are required for calculated columns and aggregates ✅ Can't use column aliases in WHERE (runs before SELECT) ✅ Can use column aliases in ORDER BY (runs after SELECT) ✅ Use quotes for aliases with spaces (but avoid spaces!) ✅ Table aliases use: FROM table_name AS alias ✅ Column aliases use: SELECT column AS alias

Coming up next: We'll learn about JOINs to combine data from multiple tables - where table aliases become essential!

Practice Exercise: Try these queries:

  1. Select first_name and last_name as "Full Name" (concatenated)
  2. Calculate annual salary (salary × 12) with alias "yearly_pay"
  3. Use table alias 'e' for employees and select e.department
  4. Count employees per department with aliases for all columns
  5. Calculate price × quantity with alias "total" and sort by it
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.