SQL Aliases

SQL

SQL Aliases : SQL aliases are used to give a table, or a column in a table, a temporary name.

  • Aliases are often used to make column names more readable.
  • An alias only exists for the duration of the 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 use the well-known Northwind sample database.

Below is a selection from 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 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

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

Example

SELECT CustomerID as ID, CustomerName AS Customer
FROM Customers;

 

Output

Number of Records: 22

ID Customer
1 Alfreds Futterkiste
2 Ana Trujillo Emparedados y helados
3 Antonio Moreno Taquería
4 Around the Horn
5 Berglunds snabbköp
6 Blauer See Delikatessen
7 Blondel père et fils
8 Bólido Comidas preparadas
9 Bon app’
10 Bottom-Dollar Marketse
11 B’s Beverages
12 Cactus Comidas para llevar
13 Centro comercial Moctezuma
14 Chop-suey Chinese
15 Comércio Mineiro
16 Consolidated Holdings
17 Drachenblut Delikatessend
18 Du monde entier
19 Eastern Connection
20 Ernst Handel
21 Familia Arquibaldo
22 FISSA Fabrica Inter. Salchichas S.A.

 

The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column.

Note:

It requires double quotation marks or square brackets if the alias name contains spaces:

Example

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

output

Number of Records: 91

Customer Contact Person
Alfreds Futterkiste Maria Anders
Ana Trujillo Emparedados y helados Ana Trujillo
Antonio Moreno Taquería Antonio Moreno
Around the Horn Thomas Hardy
Berglunds snabbköp Christina Berglund
Blauer See Delikatessen Hanna Moos
Blondel père et fils Frédérique Citeaux
Bólido Comidas preparadas Martín Sommer
Bon app’ Laurence Lebihans
Bottom-Dollar Marketse Elizabeth Lincoln

 

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

Example

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

 

Output

Number of Records: 3

CustomerName Address
Alfreds Futterkiste Obere Str. 57, 12209 Berlin, Germany
Ana Trujillo Emparedados y helados Avda. de la Constitución 2222, 05021 México D.F., Mexico
Antonio Moreno Taquería Mataderos 2312, 05023 México D.F., Mex

 

Note:

To get the SQL statement above to work in MySQL use the following:

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

 

Alias for Tables Example

The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the “Customers” and “Orders” tables, and give them the table aliases of “c” and “o” respectively (Here we use aliases to make the SQL shorter):

Example

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName=”Around the Horn” ANDc.CustomerID=o.CustomerID;

 

Output

Number of Records: 2

OrderID OrderDate CustomerName
10355 1996-11-15 Around the Horn
10383 1996-12-16 Around the Horn

 

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” ANDCustomers.CustomerID=Orders.CustomerID;

 

Output

Number of Records: 2

OrderID OrderDate CustomerName
10355 1996-11-15 Around the Horn
10383 1996-12-16 Around the Horn

 

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