MySQL - Alias

MySQL

Share

In this tutorial we will learn to create and use alias for columns and tables in MySQL.

We use alias to give a new name to the columns and tables. And the alias remains till the query executes. This helps us to create more meaningful column and table name as per our requirement.

For this tutorial we will be using the employee table that we created in the CREATE Table tutorial.

Employee Table

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com  |     7 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com  |     7 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com   |     7 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

Alias syntax for column

SELECT column_name AS alias_name
FROM table_name;

Alias syntax for table

SELECT tbl.column_name
FROM table_name tbl;

Alias for column

In the following example we will create an alias id for the employeeid column for the employee table.

mysql> SELECT employeeid AS id, firstname, lastname, email FROM employee;
+-----+-----------+----------+----------------------+
| id  | firstname | lastname | email                |
+-----+-----------+----------+----------------------+
| e01 | Yusuf     | Shakeel  | yusuf@example.com    |
| e02 | John      | Doe      | johndoe@example.com  |
| e03 | Jane      | Doe      | janedoe@example.com  |
| e04 | Tin       | Tin      | tintin@example.com   |
| e05 | Bob       | Coder    | bobcoder@example.com |
+-----+-----------+----------+----------------------+
5 rows in set (0.00 sec)

Alias for table

In the following example we will create an alias e for the employee table.

mysql> SELECT e.employeeid, e.firstname, e.lastname FROM employee e;
+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01        | Yusuf     | Shakeel  |
| e02        | John      | Doe      |
| e03        | Jane      | Doe      |
| e04        | Tin       | Tin      |
| e05        | Bob       | Coder    |
+------------+-----------+----------+
5 rows in set (0.01 sec)
Share