Table and Column Aliases in MySQL

Aliases in MySQL

Aliases are used to give columns or tables a temporary or simple name. AS keyword is used to create an alias. It is used in MySQL to make table or column names more readable. Sometimes the table or column names are very long which makes the queries also lengthy. Aliases are used to avoid lengthy queries.

Aliases are used for column names and table names.

Column Alias

The syntax for column name aliases is written below.

Syntax: 

SELECT column_name AS given_name 
FROM table_name;

It can be seen in the above syntax the given_name after AS is the alias name. If the alias name contains space then it must be enclosed inside quotes.

For example - 

SELECT column_name AS 'given name' 
FROM table_name;        

In the above syntax AS keyword is not mandatory. It is optional and it can be omitted. 

An expression can also be given an alias and multiple columns can also be given an alias. An alias can also be given to the combination of multiple columns.

For example:

SELECT id, CONCAT(firstname, ' ', lastname) AS fullname FROM students;

The above query will combine the firstname and lastname and the new name for the column will be fullname.

Output:

These Column aliases can be used in the ORDER BY, GROUP BY, and HAVING clause to refer to that column.

For example:

SELECT id, CONCAT(firstname, ' ', lastname) AS fullname 
FROM students 
ORDER BY fullname;

Table Alias

The aliases can be used to give simple and different names to tables also. Like a column alias, AS keyword is used to create a table alias. 

Syntax: 

SELECT column1, column2, …. 
FROM table_name AS given_name;

Like column alias, AS keyword is not mandatory. It is optional and it can be omitted.

The table alias can be used with the SELECT keyword for referring to the column name. They are used in queries containing joins to make it simpler (not lengthy).

For example - 

Suppose there are two tables named customers and orders.

SELECT c.customer_name, c.mobile, o.price
FROM customers AS c INNER JOIN orders AS o 
ON c.order_id=o.order_id;

To write the above query without aliases, we need to write the full name of the table again and again like,

SELECT customers.customer_name, customers.mobile, orders.price
FROM customers INNER JOIN orders
ON customers.order_id=orders.order_id;

It can be seen that without aliases the query looks very lengthy.