MySQL
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
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)
SELECT column_name AS alias_name FROM table_name;
SELECT tbl.column_name FROM table_name tbl;
In the following example we will create an alias id for the employeeid column for the employee table.
id
employeeid
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)
In the following example we will create an alias e for the employee table.
e
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)