MySQL Aliases

Aliases in MySQL are employed to provide temporary names for tables or individual columns within a table.

They are commonly used to improve the readability of column names in queries.

An alias is created using the AS keyword and exists only for the duration of that query.

Alias Column Syntax

SELECT column_name AS alias_name
FROM table_name;

Alias Table Syntax

SELECT column_name(s)
FROM table_name AS alias_name;

Demo Database

In this tutorial, we will utilize the popular Northwind sample database.

Here is a portion of the “Customers” table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK

And here is a portion of the “Orders” table:

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10354	58	8	1996-11-14	3
10355	4	6	1996-11-15	1
10356	86	6	1996-11-18	2

Alias for Columns Examples

The following SQL statement creates two aliases, one for the CustomerID column and another for the CustomerName column:

Example

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

The following SQL statement creates two aliases: one for the CustomerName column and another for the ContactName column. Note that single or double quotation marks are required if the alias name contains spaces.

Example

SELECT CustomerName AS Customer, ContactName AS “Contact Person”
FROM Customers;

The following SQL statement creates an alias named “Address” that combines four columns: Address, PostalCode, City, and Country.

Example

SELECT CustomerName, CONCAT_WS(‘, ‘, Address, PostalCode, City, Country) AS Address
FROM Customers;

Alias for Tables Example

The following SQL statement selects all orders from the customer with CustomerID=4 (Around the Horn). It utilizes the “Customers” and “Orders” tables, assigning them aliases “c” and “o” respectively to streamline the SQL query:

Example

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName=‘Around the Horn’ AND c.CustomerID=o.CustomerID;

The following SQL statement retrieves all orders from the customer with CustomerID=4 (Around the Horn) using the “Customers” and “Orders” tables, without employing aliases:

Example

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName=‘Around the Horn’ AND Customers.CustomerID=Orders.CustomerID;

Aliases can be beneficial in several scenarios:

When multiple tables are involved in a query
When functions are used in the query
When column names are lengthy or less readable
When combining two or more columns together