Alias

Alias in MySQL

Introduction
Complex queries become unreadable without short names for tables and columns.
Aliases in MySQL provide temporary names for tables and columns using the AS keyword.
They are widely used for:

• Simplifying long table/column names
• Making JOIN queries readable
• Improving query formatting

What is Alias
Alias gives a temporary name to a table or column for the duration of a single query.
The AS keyword is optional but recommended for clarity.

Basic Syntax
Column Alias:

sql

SELECT column_name AS alias_name FROM table_name; 

Table Alias:

sql

SELECT * FROM table_name AS alias_name; 

Example

sql

SELECT name AS student_name, marks AS score 

FROM Students; 

Column names display as student_name and score in the results.

Table Alias
Shortens table names in long queries.

sql

SELECT s.name, s.marks 

FROM Students AS s; 

s replaces Students throughout the query.

Column Alias without AS
The AS keyword is optional for columns.

sql

SELECT name student_name, marks score 

FROM Students; 

Both syntaxes work identically.

Multiple Column Aliases
Rename multiple columns at once.

sql

SELECT name AS full_name, 

       age AS student_age, 

       marks AS total_marks 

FROM Students; 

Table Alias with WHERE
Use an alias in conditions.

sql

SELECT s.name, s.marks 

FROM Students AS s 

WHERE s.marks > 80; 

Table Alias with JOIN
Essential for readable JOIN queries.

sql

SELECT s.name, c.course_name 

FROM Students AS s 

JOIN Courses AS c ON s.course_id = c.id; 

Alias with ORDER BY
Sort using column aliases.

sql

SELECT name, marks AS total_score 

FROM Students 

ORDER BY total_score DESC; 

Alias with GROUP BY
Group using aliases (MySQL allows).

sql

SELECT city, AVG(marks) AS avg_marks 

FROM Students 

GROUP BY city 

ORDER BY avg_marks DESC; 

Alias with Aggregate Functions
Common for calculated columns.

sql

SELECT city, 

       COUNT(*) AS student_count, 

       AVG(marks) AS avg_marks, 

       MAX(marks) AS top_score 

FROM Students 

GROUP BY city; 

Alias in Subqueries
Required for nested query references.

sql

SELECT s.name 

FROM Students s 

WHERE s.marks > ( 

    SELECT AVG(marks) FROM Students 

); 

Alias with Complex Calculations
Make formulas readable.

sql

SELECT name, 

       (marks * 0.4 + attendance * 0.6) AS final_grade 

FROM Students; 

Execution Order with Alias
Aliases available in:
SELECT (create aliases)
ORDER BY (use aliases)
HAVING (use aliases)
Not available in: WHERE, GROUP BY, FROM, JOIN

Performance Considerations

• Aliases don't affect performance
• Use short, meaningful alias names
• Consistent naming (s=students, c=courses)
• Table aliases reduce query parsing time

Important Notes

• Table aliases mandatory in FROM/JOIN
• Column aliases optional with AS
• Cannot use the same alias twice
• Case-sensitive in some collations
• Subquery results need table aliases

Example Scenario
Monthly sales report with calculations:

sql

SELECT

    s.customer_name,

    COUNT(o.order_id) AS total_orders,

    SUM(o.amount) AS total_spent,

    AVG(o.amount) AS avg_order_value

FROM Sales AS s

JOIN Orders AS o ON s.customer_id = o.customer_id

GROUP BY s.customer_name

ORDER BY total_spent DESC

LIMIT 10;

Common Mistakes

• Using column alias in the WHERE clause
• Forgetting the table alias in JOIN ON
• Same alias for multiple tables
• Long/complex alias names
• Using reserved words as aliases

Key Points to Remember

• AS keyword optional for column aliases, recommended
• Table aliases required in FROM/JOIN clauses
• Column aliases usable in ORDER BY, HAVING, SELECT
• Cannot use column aliases in WHERE/GROUP BY
• Short, consistent naming improves readability
• Essential for complex JOINs and subqueries