What Is an Alias?

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.

-- 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;

Table Aliases

Table aliases shorten table names, especially useful in multi-table joins where the same table appears multiple times.

-- 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

-- 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;

Performance Impact

-- 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