In this tutorial, we will learn about MySQL Alias. Suppose you have a query in which you are displaying the Department column and the COUNT(*) column which returns the number of employees in each department. To us programmers, we can understand what
COUNT(*) means but when you present your results to someone who doesn’t understand MySQL, it may seem gibberish to them.
To overcome such a problem, MySQL provides us with Aliases. MySQL Aliases are temporary names that can be given to columns to increase the readability of column names. Aliases act as nicknames for the columns and are valid only for that particular query. Aliases can also be given to table names.
Syntax for MySQL Alias
SELECT column_name1 AS alias_name1, column_name2 as alias_name2,.... FROM table_name;
SELECT expression FROM table_name AS alias_name;
Examples of MySQL Alias
Consider the below Employee table.
Using MySQL Alias with Columns
Let’s start with a few examples of the Alias command on columns of a data set.
How about finding out the employee ID and Name of those employees who earn more than 80000?
If you look at the Employee table, only a person who has created the table will understand what the eid column is. So how about simplifying things and increasing readability by taking ‘ID’ as the alias for the eid column. Using the
SELECT statement and the
WHERE clause, the query we use is,
SELECT eid AS ID, Name FROM Employee WHERE Salary>80000;
And we get the output as,
As you can see, instead of seeing eid as a column name in the result-set, we see ID as the column name. As mentioned earlier, this alias is temporary and valid only for this query.
Let us take another example. Let us find out the number of employees in every department. For this, we will be using the GROUP BY Clause. Normally, we’d use a query like:
SELECT Department, COUNT(*) FROM Employee GROUP BY Department;
This would display a result-set with columns Department and
COUNT(*). However, to anyone who doesn’t know what the question is, that
COUNT(*) can mean anything. How about we use an alias called ‘Number of Employees’?
SELECT Department, COUNT(*) as 'Number of Employees' FROM Employee GROUP BY Department;
When using aliases with spaces in them, be sure to use quotes. The output is as follows,
Using Aliases with Tables
We can also use aliases with tables. Let us look at the following query,
SELECT E.eid, E.Name, E.Salary FROM Employee as E;
Here we take ‘E’ as an alias for the Employee table and we use the dot (.) to access the columns from table E.
We get our output as follows,
Aliases make our results more readable and if the column name is not very apt, they help us add meaning to it. For more information, do go through the references.
- MySQL Official documentation on Aliases.