SQL Aliases
SQL Aliases
A table or a field within a table can be given a temporary name using SQL 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.
Example
SELECT CustomerID AS ID
FROM Customers;
AS is Optional
Actually, you may get the same outcome without using the AS keyword in the majority of database languages:
Example
SELECT CustomerID ID
FROM Customers;
Syntax
When alias is used on column:
SELECT column_name AS alias_name
FROM table_name;
When alias is used on table:
SELECT column_name(s)
FROM table_name AS alias_name;
Demo Database
Customers
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 |
Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 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 |
Orders
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10248 | 90 | 5 | 7/4/1996 | 3 |
| 10249 | 81 | 6 | 7/5/1996 | 1 |
| 10250 | 34 | 4 | 7/8/1996 | 2 |
Alias for Columns
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;
Using Aliases With a Space Character
Square brackets or double quotations should be used to enclose your alias if you wish it to have one or more spaces, such as “My Great Products“.
Example
Using [square brackets] for aliases with space characters:
SELECT ProductName AS [My Great Products]
FROM Products;
Example
Using “double quotes” for aliases with space characters:
SELECT ProductName AS "My Great Products"
FROM Products;
Note: While some database systems support both”” and[]; others only support one of them.
Concatenate Columns
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, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
Note: To get the SQL statement above to work in MySQL use the following:
MySQL Example
------ Example MUKAVU ------
Note: To get the SQL statement above to work in Oracle use the following:
Oracle Example
SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' || Country) AS Address
FROM Customers;
Alias for Tables
If you want to use an alias for a table, the same guidelines apply.
Example
Refer to the Customers table as Persons instead:
SELECT * FROM Customers AS Persons;
Using aliases on tables may seem pointless, but it can shorten SQL statements when you use multiple tables in your queries.
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:
- There are more than one table involved in a query
- Functions are used in the query
- Column names are big or not very readable
- Two or more columns are combined together
