SQL Aliases

SQL Aliases

SQL aliases provide temporary names to tables or columns within a query.

Aliases are frequently utilized to enhance the readability of column names.

An alias is valid only for the duration of the query in which it is defined.

Aliases are defined using the AS keyword.

Example

SELECT CustomerID AS ID FROM Customers;

AS is Optional

In many database languages, you can omit the AS keyword and achieve the same outcome.

Example

SELECT CustomerID ID FROM Customers;

Syntax

When an alias is applied to a column:

SELECT column_name AS alias_name FROM table_name;

When an alias is assigned to a table:

SELECT column_name(s) FROM table_name AS alias_name;

Demo Database

Here is a portion of the Customers and Orders tables utilized in the examples:

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

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

Example

SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;

Using Aliases With a Space Character

If you intend for your alias to include one or more spaces, such as “My Great Products“, enclose your alias with square brackets or double quotes.

Example

Utilizing [square brackets] for aliases containing space characters:

SELECT ProductName AS [My Great Products] FROM Products;

Example

Employing “double quotes” for aliases containing space characters:

SELECT ProductName AS “My Great Products” FROM Products;

 

Note: Some database systems allows both [] and “”, and some only allows one of them.

Concatenate Columns

The following SQL statement creates an alias named “Address” by combining four columns (Address, PostalCode, City, and Country):

Example

SELECT CustomerName, Address + ‘, ‘ + PostalCode + ‘ ‘ + City + ‘, ‘ + Country AS Address FROM Customers;

Note: To make the SQL statement above function in MySQL, utilize the following adjustment:

Oracle Example

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

Alias for Tables

The same principles apply when you wish to assign an alias to a table.

Example

Refer to the Customers table as Persons instead.

SELECT * FROM Customers AS Persons;

Although employing aliases on tables may seem unnecessary, it can significantly abbreviate SQLstatements, particularly when multiple tables are involved in queries.

The subsequent SQL statement retrieves all orders from the customer with CustomerID=4 (Around the Horn). We utilize the “Customers” and “Orders” tables, assigning them the table aliases of “c” and “o” respectively. In this case, aliases are utilized to condense 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 achieves the same outcome as the previous one, but without using aliases:

Example

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

 

Aliases prove beneficial in the following scenarios:

• Involvement of multiple tables in a query
• Utilization of functions within the query
• Presence of lengthy or less readable column names
• Combination of two or more columns