MySQL Aliases

MySQL aliases are used to give a table or a column a temporary name. Aliases make column names more readable and can be useful when working with functions, joins, or when column names are big or not user-friendly.

Types of Aliases

• Column Aliases - Rename a column in the result set
• Table Aliases - Rename a table in the query

Column Alias Syntax

SELECT column_name AS alias_name
FROM table_name;

The AS keyword is optional but recommended for clarity:

SELECT column_name alias_name
FROM table_name;

Table Alias Syntax

SELECT t1.column1, t2.column2
FROM table_name1 AS t1
JOIN table_name2 AS t2 ON t1.id = t2.id;

When to Use Aliases

• When column names are long or complex
• When using functions in SELECT
• When joining multiple tables
• When column names are ambiguous
• To make queries more readable

Examples

1. Basic Column Aliases

-- Without alias
SELECT first_name, last_name, salary * 12
FROM employees;

-- With alias
SELECT 
  first_name AS 'First Name',
  last_name AS 'Last Name',
  salary * 12 AS 'Annual Salary'
FROM employees;

2. Aliases with Functions

-- Calculate average salary
SELECT 
  department_id,
  AVG(salary) AS 'Average Salary',
  COUNT(*) AS 'Employee Count',
  SUM(salary) AS 'Total Salary'
FROM employees
GROUP BY department_id;

3. Table Aliases for Joins

-- Without aliases
SELECT 
  employees.first_name,
  employees.last_name,
  departments.department_name
FROM employees
INNER JOIN departments 
ON employees.department_id = departments.department_id;

-- With aliases (much cleaner)
SELECT 
  e.first_name,
  e.last_name,
  d.department_name
FROM employees e
INNER JOIN departments d 
ON e.department_id = d.department_id;

4. Self Join with Aliases

-- Find employees and their managers
SELECT 
  emp.first_name AS 'Employee',
  emp.last_name AS 'Employee Last',
  mgr.first_name AS 'Manager',
  mgr.last_name AS 'Manager Last'
FROM employees emp
LEFT JOIN employees mgr 
ON emp.manager_id = mgr.employee_id;

5. Aliases with Subqueries

-- Using subquery with alias
SELECT 
  dept.department_name,
  emp_count.total_employees
FROM departments dept
INNER JOIN (
  SELECT department_id, COUNT(*) AS total_employees
  FROM employees
  GROUP BY department_id
) emp_count 
ON dept.department_id = emp_count.department_id;

6. Aliases with Calculated Columns

-- Calculate age from birth date
SELECT 
  first_name,
  last_name,
  birth_date,
  YEAR(CURDATE()) - YEAR(birth_date) AS age
FROM employees;

Best Practices

• Use descriptive alias names
• Use the AS keyword for clarity
• Use quotes for aliases with spaces or special characters
• Keep aliases consistent across queries
• Use short but meaningful table aliases

Common Issues and Solutions

Sample Query with All Features

SELECT 
  c.customer_id AS 'ID',
  CONCAT(c.first_name, ' ', c.last_name) AS 'Customer Name',
  COUNT(o.order_id) AS 'Total Orders',
  SUM(oi.quantity * oi.unit_price) AS 'Total Spent',
  CASE 
    WHEN SUM(oi.quantity * oi.unit_price) > 1000 THEN 'VIP'
    WHEN SUM(oi.quantity * oi.unit_price) > 500 THEN 'Regular'
    ELSE 'New'
  END AS 'Customer Type'
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id
HAVING COUNT(o.order_id) > 0
ORDER BY 'Total Spent' DESC;