How to Use MySQL ‘alias’ to Make Queries Easier to Read

This article will explain and demonstrate the use of aliases in MySQL (and MariaDB).

MySQL queries can get pretty gnarly – especially if you’re selecting multiple columns from multiple tables. An alias statement is a great tool for simplifying these queries.

An alias is just another name for the column or table in question, which you can use to refer to the column or table by. It’s a nickname that can be used to quickly refer to something complex to save time when writing queries.

MySQL Column Alias

Column Alias Syntax

A column alias is just that – an alias for a column. MySQL uses the AS keyword to assign the alias. Here’s the syntax:

SELECT 
    [column_name | expression] AS alias_name
FROM table_name;

Note that:

• The AS keyword, which is used to create a column alias, is used as part of a SELECT statement
  • You’ll be selecting data from a table and giving the columns holding that data the alias
• column_name | expression is the name of the column or expression that you want to create the alias for
• alias_name will be the name of the alias
  • If you use spaces or other non-alphanumeric characters for your alias, you may need to quote the alias name.
• You CANNOT assign an alias in a MySQL WHERE clause – the alias can only be assigned after the column values have been evaluated.

Column Alias Example

These examples are written for a simple table called addresses with a street_address column and a postcode column – and we want to write a query that returns a single, merged result containing the values from both columns:

SELECT 
    CONCAT_WS(', ', street_address, postcode)
FROM
    addresses;

The CONCAT_WS command joins the value of the street_address and postcode columns and returns them as a single column.

When executed, the column name for the results will be the following:

CONCAT_WS(', ', street_address, postcode)

This is messy – hard to read, hard to retype. So let’s give it an alias:

SELECT
    CONCAT_WS(', ', street_address, postcode) AS `full_address`
FROM
    addresses;

The column name in the results will now be full_address – easy to ready, easy to type. That’s what aliases are for!

MySQL Table Alias

Table Alias Syntax

Table aliases work pretty much the same way and also use the AS keyword:

table_name AS table_alias

Table Alias Example

Below, the table addresses is given the alias a:

SELECT * FROM addresses AS a;

Why is this useful?

Aliases are vital when you are querying multiple tables at the same time.

If you are querying two tables with an INNER JOIN, LEFT JOIN, or RIGHT JOIN – and those tables have a column with the same name in each – MySQL will have no way of knowing which identically named column in which table you are referring to. Therefore, you need to specify which column in which table each time you refer to the column, which can be laborious with long table names.

By aliasing each table, you can make a shortcut:

SELECT a.address, a.postcode, p.name
FROM addresses AS a
INNER JOIN people AS p ON a.household_id = p.household_id;

Above, we can see two tables being queried – addresses and people.

Each table has a column called household_id – so we need to refer to the full location of the column whenever it is referred to. By using aliases, we only need to use a and p to refer to addresses and people – making the query less cluttered.

These are only simple examples – as queries get more complex, aliases can make them far simpler to type and read. For example, a table alias could be used with MERGE queries to create named, merged tables.

For more on using aliases in MySQL, check out the official documentation.