Aliasing Columns and Tables in SQL

Table of Contents

1. Introduction
2. What is an Alias in SQL?
3. Why Use Aliases?
4. Syntax of Aliases in SQL
5. Column Aliases with AS
6. Column Aliases Without AS
7. Table Aliases with AS
8. Table Aliases Without AS
9. Aliases in SELECT Clause
10. Aliases in ORDER BY and GROUP BY
11. Aliases in WHERE and HAVING
12. Using Aliases in Joins
13. Aliasing with Subqueries
14. Nested Aliases
15. Quoting Aliases with Spaces or Reserved Words
16. Real-World Example: Improving Readability
17. Common Mistakes with Aliases
18. Performance and Execution Notes
19. Best Practices for Aliases
20. Summary and What’s Next

1. Introduction

Aliases are temporary names that you assign to columns or tables in an SQL query. They help make your queries more readable, manageable, and meaningful — especially when dealing with complex expressions, joins, or subqueries.

2. What is an Alias in SQL?

An alias is an alternate name given to a column or table for the duration of a query. Aliases are created using the AS keyword or by simply placing the alias name after the column/table.

3. Why Use Aliases?

• Improve readability of results
• Rename complex expressions
• Clarify meaning of derived columns
• Shorten long table names in joins
• Avoid ambiguity in self-joins

4. Syntax of Aliases in SQL

Column alias:

sqlCopyEditSELECT column_name AS alias_name
FROM table_name;

Table alias:

sqlCopyEditSELECT t.column_name
FROM table_name AS t;

5. Column Aliases with AS

sqlCopyEditSELECT first_name AS name, salary AS income
FROM employees;

This renames first_name to name and salary to income in the output.

6. Column Aliases Without AS

SQL allows omitting AS:

sqlCopyEditSELECT first_name name, salary income
FROM employees;

It works the same, but using AS improves clarity and is more readable.

7. Table Aliases with AS

sqlCopyEditSELECT e.name, d.name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

Here, e and d are shorthand aliases for the respective tables.

8. Table Aliases Without AS

You can skip AS for tables too:

sqlCopyEditSELECT e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

This is common in industry, especially in complex joins.

9. Aliases in SELECT Clause

sqlCopyEditSELECT salary * 12 AS annual_salary
FROM employees;

Use aliases to rename derived or calculated columns.

10. Aliases in ORDER BY and GROUP BY

You can reference column aliases in ORDER BY and GROUP BY:

sqlCopyEditSELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

Note: Some DBMSs allow using positional references too (e.g., ORDER BY 2).

11. Aliases in WHERE and HAVING

⚠️ Important: You cannot use column aliases in WHERE because WHERE is processed before SELECT.

sqlCopyEdit-- INVALID
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000; -- ❌ Won't work

✅ Use in HAVING or repeat the expression:

sqlCopyEditSELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;

12. Using Aliases in Joins

sqlCopyEditSELECT e.name, d.name AS department_name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

Makes complex joins more readable and manageable.

13. Aliasing with Subqueries

sqlCopyEditSELECT *
FROM (
  SELECT product_id, SUM(quantity) AS total_sold
  FROM order_items
  GROUP BY product_id
) AS sales_summary;

Subqueries must be aliased — it’s required in SQL.

14. Nested Aliases

sqlCopyEditSELECT ss.product_id, ss.total_sold
FROM (
  SELECT product_id, SUM(quantity) AS total_sold
  FROM order_items
  GROUP BY product_id
) AS ss
WHERE ss.total_sold > 100;

You can reference subquery aliases outside the nested query.

15. Quoting Aliases with Spaces or Reserved Words

If alias names contain spaces, special characters, or are SQL keywords, you must quote them:

sqlCopyEditSELECT first_name AS "First Name", salary AS "Monthly Salary"
FROM employees;

Use double quotes " " (PostgreSQL, standard SQL) or backticks ` (MySQL).

16. Real-World Example: Improving Readability

Before aliasing:

sqlCopyEditSELECT employees.first_name, departments.name, salaries.amount * 12
FROM employees
JOIN departments ON employees.dept_id = departments.id
JOIN salaries ON employees.id = salaries.emp_id;

After aliasing:

sqlCopyEditSELECT e.first_name AS name, d.name AS department, s.amount * 12 AS annual_salary
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id
JOIN salaries AS s ON e.id = s.emp_id;

Much cleaner and easier to understand.

17. Common Mistakes with Aliases

• Using alias in WHERE clause (not allowed)
• Forgetting to alias subqueries (syntax error)
• Using ambiguous column names without table alias
• Omitting alias in multi-join queries, leading to conflicts

18. Performance and Execution Notes

• Aliases do not affect performance
• They exist only during query execution
• They’re not stored in the database schema
• Use them purely for clarity and convenience

19. Best Practices for Aliases

• Always use aliases in joins or subqueries
• Prefer AS for clarity, especially with columns
• Use lowercase or snake_case for aliases (unless displaying)
• Avoid cryptic single-letter aliases unless within tight scope
• Name derived columns meaningfully (e.g., total_revenue, avg_rating)

20. Summary and What’s Next

Aliases in SQL are temporary labels that improve query readability and usability. Whether you’re simplifying long names or formatting reports, aliases help you write cleaner and more intuitive SQL queries.