Using MySQL Column Aliases and Table Aliases

MySQL alias is a feature that allows us to give a temporary alternative name for a database table or column. These alternative names allow for easier readability and provide extra functionality when working with MySQL JOINS.

This guide will look at how to implement both the column and table aliases in MySQL.

Before we dive into the tutorial, ensure you have a MySQL server installed and accessible on your system. To maximize the learning experience, we recommend you download the MySQL Sakila sample database.

MySQL Column Alias

MySQL supports two types of aliases: column and table aliases.

Let us start with column aliases and learn how to implement them.

The general syntax for a column alias in MySQL is:

col_name AS alias_name

To assign an alias name to a column, we use the AS keyword followed by the name we wish to use. It is best to enclose your alias name in quotes for words that contain special characters or native MySQL keywords.

NOTE: The AS keyword is optional, and you can omit it in an SQL statement. However, if others are going to read your code, it is better to add the AS keyword as it is more descriptive.

Also, the alias name given to a column or table is only effective in the scope of the SQL statement in which it is used.

Example: Column Alias

Let us use an example to illustrate how we can use the column aliases in our SQL operations. For this example, I am going to use the payment table in the Sakila sample database.

We can get the maximum value of the amount column and add the result to an alias name as shown in the query below:

USE sakila;

SELECT payment_id, rental_id, MAX(amount) AS “costly” FROM payment;

Once we execute the above query, we will get a column name titled “costly”, containing the highest amount paid in the payment table.

The output is as shown below:

Database changed

+------------+-----------+--------+

| payment_id | rental_id | costly |

+------------+-----------+--------+

| 1 | 76 | 11.99 |

+------------+-----------+--------+

1 row in set (0.00 sec)

This makes it easier to understand not only what the column contains but what the function is doing.

MySQL Table Alias

The other type of alias supported by MySQL is table aliases. We mainly use table aliases to give shorter names to the tables, making the queries shorter and easier to read. We can als0 use it when listing a table name more than once, such as when performing SQL JOINS.

The general syntax for creating a table alias is as shown below:

tb_name AS tb_alias

As mentioned above, the AS keyword is optional when creating aliases, and you can omit it. However, you should use it because it makes things easier to read and understand.

Example: Table Alias

Let us illustrate how to use a table alias in a real-world database. For example, let us select values in the address table of the Sakila database using an alias.

Consider the query below:

SELECT district FROM address AS addr LIMIT 5;

Once we execute the query, we get the districts in the address table, which we referenced using the alias addr.

The output is below:

+----------+

| district |

+----------+

| Alberta |

| QLD |

| Alberta |

| QLD |

| Nagasaki |

+----------+

5 rows in set (0.00 sec)

NOTE: Remember that the alias name assigned to a table or column is only valid in the SQL statement it is created.

However, we can reference it using to get data to and from it. For example, to get a column in the table address (addr as the alias), we can use a query as shown below:

SELECT addr.address_id, addr.city_id FROM address as addr LIMIT 5;

The query above runs successfully and gives us the data as shown in the output below:

+------------+---------+

| address_id | city_id |

+------------+---------+

| 56 | 1 |

| 105 | 2 |

| 457 | 3 |

| 491 | 4 |

| 332 | 5 |

+------------+---------+

5 rows in set (0.01 sec)

Example 2: Table Alias on Join

A popular use of table aliases is when performing SQL JOINS. An example of how it can be applied to the Sakila sample database is shown in the query below:

SELECT cust.customer_id, CONCAT_WS(" ", first_name, last_name) AS name FROM customer AS cust INNER JOIN inventory AS invt ON cust.customer_id = invt.store_id GROUP BY first_name;

The above query gives us the output shown below:

+-------------+------------------+

| customer_id | name |

+-------------+------------------+

| 1 | MARY SMITH |

| 2 | PATRICIA JOHNSON |

+-------------+------------------+

2 rows in set (0.02 sec)

Conclusion

This tutorial illustrated how to use column and table aliases to perform operations such as SQL JOINS or increase your SQL queries’ readability.