SQL Aliases (AS)

An alias is a temporary name you give to a column or a table for the length of one query. Column aliases make result headers readable; table aliases make joins short and are required when a table appears twice. Neither changes anything in the database.

Column aliases

SELECT first_name AS name,       salary * 12 AS annual_salary,       hire_date   AS joinedFROM employeesWHERE dept_id = 3;

AS is optional: salary * 12 annual_salary works the same. Writing AS makes the intent obvious and avoids the missing-comma trap.

Aliases with spaces

Wrap an alias in quotes or backticks when it contains spaces:

SELECT customer_name AS 'Customer Name',       city          AS "Home City"FROM customersLIMIT 3;

Table aliases

A table alias is a short name for a table, written after it in FROM or JOIN. Columns are then written as alias.column:

SELECT e.first_name, d.dept_name, d.locationFROM employees AS eJOIN departments AS d ON d.dept_id = e.dept_idWHERE d.location = 'Pune';

With aliases, a column that exists in both tables, such as dept_id, is never ambiguous. Without a prefix MySQL refuses the query:

SELECT dept_id, first_name, dept_nameFROM employeesJOIN departments ON departments.dept_id = employees.dept_id;

ERROR 1052 (23000): Column 'dept_id' in field list is ambiguous

When an alias is required: self join

When the same table appears twice, aliases are the only way to tell the copies apart. Each employee with their manager’s name:

SELECT e.first_name AS employee,       m.first_name AS managerFROM employees eJOIN employees m ON m.emp_id = e.manager_idORDER BY e.emp_id;

Where can an alias be used?

SELECT category, COUNT(*) AS productsFROM productsGROUP BY categoryHAVING products >= 3ORDER BY products DESC;

SELECT product_name   AS Product,       price          AS 'Price (INR)',       price * stock  AS 'Stock Value'FROM products;

Alias FAQ