MySQL Aliases
MySQL Aliases
A table or a column within a table can be given a temporary name by using aliases.
To make column names easier to comprehend, aliases are frequently utilized.
An alias is only present while that query is running.
By using the AS keyword, an alias is established.
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
We’ll be using the well-known Northwind sample database in this tutorial.
A sample from the “Customers” table is shown below:
| 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 a selection from 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
Two aliases are created for the CustomerID and CustomerName columns by the SQL statement that follows:
Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
Two aliases are created for the CustomerName and ContactName columns by the SQL statement that follows.
Note: If there are spaces in the alias name, you must enclose it in single or double quote marks:
Example
SELECT CustomerName AS Customer, ContactName AS "Contact Person"
FROM Customers;
Address, PostalCode, City, and Country are the four columns that are combined into one alias called “Address” by the SQL statement that follows:
Example
SELECT CustomerName, CONCAT_WS(', ', Address, PostalCode, City, Country) AS Address
FROM Customers;
Alias for Tables Example
The orders from the client with CustomerID=4 (Around the Horn) are all selected by the SQL statement that follows. Here, we utilize table aliases to shorten the SQL by using the “Customers” and “Orders” tables, respectively, with the table names “c” and “o”:
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 is the same as above, but without 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 useful when:
A query inherently involves many tables.
In the query, functions are utilized.
The column names are large or difficult to read.
The merging of two or more columns
