• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Aliases

← Previous Next →

In MySQL, an alias is a temporary name given to a table or column in a SQL query. Aliases improve query readability, make column names more descriptive, and help simplify complex queries.

Aliases are especially useful when performing calculations, joins, or aggregations, as they allow you to refer to results with a friendly or meaningful name.

Column Aliases

A column alias assigns a temporary name to a column in the output. This is often used for readable headings or calculated values.

Basic Syntax

SELECT column_name AS alias_name
FROM table_name;

  • AS is optional; you can also write:

SELECT column_name alias_name
FROM table_name;

Example 1 – Simple Alias

SELECT name AS StudentName, age AS StudentAge
FROM students;

  • The output will show columns as StudentName and StudentAge instead of name and age.

Example 2 – Calculated Column Alias

SELECT name, score, score * 10 AS AdjustedScore
FROM students;

  • Creates a new column AdjustedScore, calculated by multiplying score by 10.

Table Aliases

A table alias assigns a temporary name to a table in a query. This is helpful in complex queries like joins, subqueries, or self-joins.

Basic Syntax

SELECT t.column_name
FROM table_name AS t;

  • t is the alias for the table.

  • Using aliases can make queries shorter and easier to read.

Example 1 – Simple Table Alias

SELECT s.name, s.age
FROM students AS s;

  • s acts as a shorthand for students.

  • You can now refer to columns as s.name and s.age.

Example 2 – Using Aliases in Joins

SELECT s.name, c.course_name
FROM students AS s
JOIN courses AS c
ON s.course_id = c.id;

  • s and c are table aliases.

  • Aliases make joins cleaner, especially with multiple tables.

Example 3 – Self Join with Table Alias

Self joins require table aliases to differentiate between the same table used multiple times:

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees AS e1
JOIN employees AS e2
ON e1.manager_id = e2.id;

  • e1 refers to the employee, and e2 refers to the manager.

  • Aliases prevent ambiguity when using the same table multiple times.

Using Aliases in Subqueries

Aliases are often used with subqueries to name the result of a query:

SELECT s.StudentName, avg_score.AverageScore
FROM students AS s
JOIN (
    SELECT course_id, AVG(score) AS AverageScore
    FROM students
    GROUP BY course_id
) AS avg_score
ON s.course_id = avg_score.course_id;

  • avg_score is the alias for the subquery.

  • AverageScore is a column alias for the aggregated value.

  • This makes the query readable and maintainable.

Rules for Aliases

  1. Aliases are temporary and only exist during the query execution.

  2. Aliases cannot contain spaces unless enclosed in backticks:

SELECT name AS `Student Name`
FROM students;

  1. Aliases can include numbers and underscores.

  2. Column aliases can be used in ORDER BY, but not always in WHERE clauses.

  3. Table aliases are required in self-joins to distinguish the same table.

Combining Column and Table Aliases

Aliases can be combined for clearer queries:

SELECT s.name AS StudentName, c.name AS CourseName
FROM students AS s
JOIN courses AS c
ON s.course_id = c.id
WHERE s.age > 18
ORDER BY StudentName;

  • Column aliases make the output readable.

  • Table aliases simplify references in joins.

  • ORDER BY uses the column alias to sort results.

Practical Scenarios

  1. Readable output for reports:

SELECT name AS StudentName, score AS FinalScore
FROM students;

  • The report displays descriptive column names instead of database column names.

  1. Simplifying long table names:

SELECT s.name, c.course_name
FROM student_enrollment AS s
JOIN course_details AS c
ON s.course_id = c.id;

  • Short table aliases make complex queries easier to read.

  1. Aggregated results with aliases:

SELECT course_id, COUNT(*) AS TotalStudents
FROM students
GROUP BY course_id;

  • The TotalStudents alias clarifies the meaning of the count column.

  1. Self join example – employee and manager:

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees AS e1
JOIN employees AS e2
ON e1.manager_id = e2.id;

  • Table aliases distinguish between two instances of the same table.

Advantages of Using Aliases

  • Improves readability of SQL queries.

  • Simplifies complex queries involving joins and subqueries.

  • Provides meaningful column names in the result set.

  • Reduces repetition of long table names.

  • Essential for self-joins and subquery results.

Summary of the Tutorial

  • Column aliases rename columns in the output for readability or calculations.

  • Table aliases rename tables temporarily to simplify query references.

  • Aliases are widely used in joins, subqueries, aggregations, and reporting.

  • Aliases are temporary and exist only for the duration of the query.

  • Combining table and column aliases produces clean, readable, and maintainable SQL queries.

Mastering aliases improves your ability to write professional SQL queries, especially when dealing with complex joins, aggregations, and reporting tasks.

Practice Questions

  1. Select the names of students with an alias StudentName.

  2. Select student names and scores, giving the score column an alias FinalScore.

  3. Select student names and course names from a join, using aliases s for students and c for courses.

  4. Select employee names and their managers’ names using a self join with table aliases.

  5. Count the number of students in each course, giving the count an alias TotalStudents.

  6. Select student names and their adjusted scores (score * 1.1) with a column alias AdjustedScore.

  7. Select names from a table with an alias s and sort by the alias StudentName.

  8. Select students with ages above 18, alias the age column as StudentAge, and use table alias s.

  9. Use a subquery to calculate average scores per course, giving the subquery an alias avg_score and the column an alias AverageScore.

  10. Select first and last names from employees, giving each column a descriptive alias (e.g., FirstName, LastName) for readability.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top