How to Use Aliases in MySQL

08/04/2021

Contents

In this article, you will learn how to use Aliases in MySQL.

Using Aliases in MySQL

MySQL aliases are used to give a temporary name to a column or table in a SQL query. Aliases can make the SQL statements more readable and concise.

Syntax

The basic syntax of aliases in MySQL is as follows:

SELECT column_name AS alias_name
FROM table_name AS alias_name;

In this syntax, column_name is the name of the column you want to assign an alias to, and alias_name is the temporary name you want to give to the column. Similarly, table_name is the name of the table you want to assign an alias to.

Examples

Suppose we have a table called “employees” with columns “id”, “name”, and “salary”. We want to select all employees and give the “salary” column an alias called “Monthly Salary”.

SELECT name, salary AS `Monthly Salary`
FROM employees;

In this example, the query will return the names and salaries of all employees, with the “salary” column temporarily named as “Monthly Salary”.

And suppose we have two tables called “students” and “courses”, with columns “id”, “name”, and “course_id”. We want to select all students and their corresponding course names.

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

In this example, the query uses aliases to give the “students.name” column a temporary name of “student_name”, and the “courses.name” column a temporary name of “course_name”. The query joins the “students” and “courses” tables on the “course_id” and “id” columns, respectively.

Aliases for table names

You can also assign aliases to table names in MySQL. Here is an example:

SELECT e.name, e.salary, d.name AS department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.id;

In this example, the “employees” and “departments” tables are given aliases of “e” and “d”, respectively. The query returns the names and salaries of all employees, along with the name of their corresponding department.

Aliases with aggregate functions

Aliases can also be used with aggregate functions in MySQL. Here is an example:

SELECT AVG(salary) AS avg_salary
FROM employees;

In this example, the AVG() function is used to calculate the average salary of all employees. The result of the function is given a temporary name of “avg_salary”.

Aliases with subqueries

Aliases can also be used with subqueries in MySQL. Here is an example:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees) AS avg_salary;

In this example, the subquery calculates the average salary of all employees. The result of the subquery is given a temporary name of “avg_salary”. The outer query selects the names and salaries of all employees whose salary is greater than the average salary.

#MySQL