Alias in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Alias in MySQL with Examples

In this article, I am going to discuss Alias in MySQL with Examples. Please read our previous article where we discussed the LIMIT Clause in MySQL with Examples.

MySQL Alias

In MySQL, an Alias means a temporary name given to a table or a column. The purpose of Aliases is to make table or column names more readable. The Aliases are more useful when multiple columns or multiple tables are used in a single query. Also, if the table names or column names are big or not readable. The AS keyword is used to define the Alias in SQL statement.

Example to understand Alias in MySQL:

We are going to use the following Employee table to understand Aliases in MySQL with Examples.

Please use the following SQL Script to create the company database and employee table with the required records.

CREATE DATABASE company;
USE company;

CREATE TABLE employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');

Giving Alias to the Table name in MySQL

In the below SQL Statement, we have given an alias i.e. emp to the employee table and then using the alias name we are accessing the column names. Notice that here we used emp.Name and emp.Department, etc. in SQL statement. Here emp is an Alias for employee table.

SELECT emp.Name, emp.Department, emp.age, emp.Salary FROM Employee AS emp;

When you execute the above query, you will get the below result set.

Note: Alias’s use is more common in joins, where we prefix alias and dot to a column name to avoid conflicts of two similar column names from two different tables.

Giving Alias to Column Names

Let’s execute another SQL statement to display the total number of employees in the table. Following is the SQL statement which will give you the total number of employees present in the employee table.

SELECT COUNT(*) FROM employee;

Now execute the above SQL statement, you will get the total number of employees in the employee table and you will get the below output.

But notice that in this case, the column name is COUNT(*) which is the function name, which doesn’t make any sense for the result set. To make the result set more readable we can give an alias for the same as shown in the below SQL statement. Here we are giving the alias as TotalEmployees to the COUNT(*) function.

SELECT COUNT(*) AS TotalEmployees FROM Employee;

Here I am not using quotes for my Alias name as the string contains a single word. If you are using space for the Alias name you can enter the string using single quotes. Now run the SQL statement, and this time, the column name clearly indicates that the result value is TotalEmployees in the table as shown in the below image.

Joining two columns together in MySQL

If we are joining two columns together using the CONCAT function to display the name and department of the employees together, we will use, CONCAT(name, ‘_’, department). The following SQL Script joins two columns.

SELECT Id, CONCAT(Name, ‘_’, Department), Gender, Salary, Age FROM Employee;

Once you execute the above query, you will get the following output.

Here, the function CONCAT doesn’t make any sense. So to make it more readable, we can use an alias as shown in the below SQL Statement.

SELECT Id, CONCAT(Name, ‘_’, Department) AS Name_Department, Gender, Salary, Age FROM Employee;

Now execute the SQL statement. And notice this time we get Name_Department as the column name as shown in the below image.

Also notice that in the SQL statement, I didn’t use single quotes as it’s a one-word string. But in the below example we use Name Department as two words and hence we use a single quote.

SELECT Id, CONCAT(Name, ‘_’, Department) AS ‘Name Department’, Gender, Salary, Age FROM Employee;

Once you execute the above SQL Query, you will get the below output and see how the column name is a two-word string.

In the next article, I am going to discuss Operators and Conditions in MySQL with Examples. Here, in this article, I try to explain Alias in MySQL with Examples. I hope you enjoy this article. I would like to have your feedback. Please post your feedback, question, or comments about this article.