MySQL Alias – An Easy Way to Setup Aliases in MySQL

By /
Mysql Alias

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

For Column

SELECT column_name1 AS alias_name1, column_name2 as alias_name2,.... FROM table_name; Code language: SQL (Structured Query Language) (sql)

For Table

SELECT expression FROM table_name AS alias_name;Code language: SQL (Structured Query Language) (sql)

Examples of MySQL Alias

Consider the below Employee table.

Group By Employee Table
Employee Table

Using MySQL Alias with Columns

Let’s start with a few examples of the Alias command on columns of a data set.

Example 1

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;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Alias Column Example 1

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.

Example 2

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

When using aliases with spaces in them, be sure to use quotes. The output is as follows,

Alias Column Example 2

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;Code language: SQL (Structured Query Language) (sql)

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,

Alias Table Example

Conclusion

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.

References