MySQL Aliases (AS)

An alias in MySQL gives a temporary name to a column or table within a query. The alias exists only for the duration of that query and does not change the actual name in the database. Aliases are created using the AS keyword.

Why Use Aliases?

Column names from functions or expressions (like COUNT(*) or emp_salary * 12) are hard to read in results. Aliases give these columns meaningful, readable names. Table aliases are especially useful when writing queries that involve multiple tables with long names.

Types of Aliases

• Column Alias — Renames a column in the result set
• Table Alias — Renames a table within the query for shorter references

Column Alias Syntax

SELECT column_name AS alias_name
FROM table_name;

Example: Column Alias

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    emp_salary DECIMAL(10,2)
);

INSERT INTO employees VALUES (1, 'Deepak Rao', 40000);
INSERT INTO employees VALUES (2, 'Sunita Bose', 55000);

SELECT emp_name AS Name, emp_salary AS Monthly_Salary
FROM employees;

Result:

Name         | Monthly_Salary
-------------+---------------
Deepak Rao   | 40000.00
Sunita Bose  | 55000.00

Alias for Calculated Columns

SELECT emp_name AS Name,
       emp_salary * 12 AS Annual_Salary
FROM employees;

Result:

Name         | Annual_Salary
-------------+--------------
Deepak Rao   | 480000.00
Sunita Bose  | 660000.00

Without the alias, the column header would display emp_salary * 12, which is not readable.

Column Alias with Spaces

If the alias contains spaces, enclose it in double quotes or backticks.

SELECT emp_name AS "Employee Name",
       emp_salary AS `Monthly Pay`
FROM employees;

Table Alias Syntax

SELECT t.column_name
FROM table_name AS t;

Example: Table Alias

SELECT e.emp_name, e.emp_salary
FROM employees AS e
WHERE e.emp_salary > 45000;

Here e is the alias for the employees table. Instead of writing the full table name repeatedly, the short alias e is used.

Aliases in JOIN Queries

Table aliases become very useful when joining multiple tables.

SELECT e.emp_name, d.dept_name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.dept_id;

Using e and d makes the query shorter and easier to read than repeating full table names.

The AS Keyword is Optional

MySQL allows aliases without the AS keyword. Both forms work:

SELECT emp_name Name FROM employees;
SELECT emp_name AS Name FROM employees;

Using AS is recommended for clarity and readability.

Alias Scope

Column aliases defined in SELECT cannot be used in the WHERE clause of the same query because WHERE is processed before SELECT. However, they can be used in ORDER BY.

-- This works:
SELECT emp_salary * 12 AS Annual_Salary
FROM employees
ORDER BY Annual_Salary DESC;

-- This does NOT work:
SELECT emp_salary * 12 AS Annual_Salary
FROM employees
WHERE Annual_Salary > 500000;  -- Error: column not recognized here

Key Points

• Aliases give temporary names to columns or tables in a query.
• The AS keyword is used to create an alias, though it is optional.
• Column aliases improve readability of results, especially for expressions.
• Table aliases simplify long query syntax, especially in JOINs.
• Aliases with spaces require double quotes or backticks.
• Column aliases can be used in ORDER BY but not in WHERE.