Aliases (AS)
Give temporary names to columns and tables using the AS keyword for cleaner, more readable queries.
- Column Aliases β Rename result columns for clarity
- Table Aliases β Shorten table references in joins
- Expression Aliases β Label calculated fields Make your queries self-documenting with meaningful names.
What Is an Alias?
DfAlias (AS)
An alias is a temporary name assigned to a column or table within a SQL query. It does not modify the underlying database schema β it only changes how the name appears in the query result or within the query itself.
SELECT
first_name AS "First Name",
last_name AS "Last Name",
salary * 0.1 AS "Bonus Amount"
FROM employees;
Column Aliases
Column aliases rename columns in the output. They improve readability when working with calculated fields or verbose column names.
DfColumn Alias Syntax
SELECT expression AS alias_name FROM table_name;
The alias takes effect only for the duration of the query.
-- Basic column renaming
SELECT
first_name AS employee_first,
last_name AS employee_last,
salary AS annual_salary
FROM employees;
-- Aliases for calculated expressions
SELECT
product_name,
unit_price,
quantity,
unit_price * quantity AS line_total,
ROUND(unit_price * quantity, 2) AS formatted_total
FROM order_items;
-- String concatenation with alias
SELECT
CONCAT(first_name, ' ', last_name) AS full_name,
email AS contact_email
FROM customers;
| Original Expression | Alias | Use Case |
|---|---|---|
salary * 0.1 | bonus | Calculated field |
CONCAT(first, ' ', last) | full_name | String operation |
DATEDIFF(NOW(), hire_date) | tenure_days | Date calculation |
CASE WHEN ... END | status_label | Conditional logic |
Table Aliases
Table aliases shorten table names, especially useful in multi-table joins where the same table appears multiple times.
DfTable Alias
A table alias assigns a short identifier to a table within a query. It is essential for self-joins and improves readability in complex joins.
-- Table aliases in a join
SELECT
c.first_name,
c.last_name,
o.order_date,
o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;
-- Self-join using table aliases
SELECT
e.first_name AS employee,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Multi-table join with aliases
SELECT
c.customer_name,
o.order_date,
p.product_name,
oi.quantity,
oi.unit_price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;
-- This FAILS: alias 'emp_salary' not recognized in WHERE
SELECT first_name AS emp_salary FROM employees WHERE emp_salary > 50000;
-- Correct: use the original column name
SELECT first_name AS emp_salary FROM employees WHERE salary > 50000;
-- Correct: use HAVING for aggregate aliases
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
Alias Rules and Best Practices
DfAlias Naming Rules
- Aliases can contain letters, numbers, and underscores
- Avoid reserved keywords as aliases
- Use double quotes for aliases with spaces or special characters
- Alias names are case-insensitive in most databases
-- Good alias practices
SELECT
e.first_name AS first, -- Short and clear
e.last_name AS last,
d.name AS department, -- Meaningful name
e.salary AS salary -- Redundant but valid
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- Avoid cryptic aliases
-- Bad:
SELECT a, b, c FROM x JOIN y ON a = d;
-- Good:
SELECT e.first_name, e.salary, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
| Best Practice | Why It Matters |
|---|---|
| Use descriptive aliases | Makes results self-documenting |
| Keep aliases short | Reduces query clutter |
| Use table aliases in joins | Prevents column ambiguity |
| Avoid reserved keywords | Prevents syntax errors |
| Use quotes for spaces | Handles special characters |
Performance Impact
DfAlias Performance
Aliases have zero performance impact. The database optimizer works with original column and table names internally. Aliases are purely a display and readability feature.
-- Both queries perform identically
SELECT first_name AS name, salary AS pay FROM employees WHERE salary > 50000;
SELECT first_name, salary FROM employees WHERE salary > 50000;
Practice Exercises
Exercise 1: Write a query using table aliases to join customers with orders and display the customer name, order date, and total.
-- Solution
SELECT
c.first_name,
c.last_name,
o.order_date,
o.total AS order_total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
Exercise 2: Create aliases for complex calculations in an employee bonus report.
-- Solution
SELECT
employee_id,
first_name,
last_name,
salary,
salary * 0.1 AS bonus,
salary + (salary * 0.1) AS total_compensation,
ROUND(salary * 12, 2) AS annual_salary
FROM employees;
Exercise 3: Use table aliases in a three-table join between customers, orders, and products.
-- Solution
SELECT
c.first_name AS customer,
o.order_date,
p.product_name,
oi.quantity,
oi.unit_price
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;
Summary
Key Takeaways
- Column aliases rename columns in the result set for readability
- Table aliases shorten table references, essential for self-joins
- The AS keyword is optional for columns and tables
- Aliases cannot be used in WHERE β use the original column name
- Aliases have no performance impact on query execution
- Use meaningful alias names to make queries self-documenting
| Topic | Syntax | Example |
|---|---|---|
| Column Alias | SELECT col AS alias | SELECT salary AS pay |
| Table Alias | FROM table AS alias | FROM employees e |
| Expression Alias | SELECT expr AS alias | SELECT price * qty AS total |
| No AS Keyword | SELECT col alias | SELECT salary pay |