SQL Alias

An Alias is a shorthand for a table or column name.

Aliases reduce the amount of typing required to enter a query.

Complex queries with aliases are generally easier to read.

Aliases are useful with JOINs and aggregates: SUM, COUNT, etc.

Example

#

List all customer names with a Name column.

SELECT FirstName + ' ' + LastName AS Name,
       City, Country
  FROM Customer

Note: 'Name' is a column alias. It only exists for the duration of the query.

Result:  91 records
Name City Country
Maria Andersi Berlin Germany
Ana Trujillo México D.F. Mexico
Antonio Moreno México D.F. Mexico
Thomas Hardy London UK
Christina Berglund Luleå Sweden

Syntax

Syntax for a column alias.

SELECT column-name AS alias-name
  FROM table-name
 WHERE condition

Syntax for a table alias.

SELECT column-names
  FROM table-name alias-name
 WHERE condition

More Examples

SELECT with Column and Table Aliases

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List the number of customers in each country.
Display results with easy-to-understand column headers.
SELECT C.Country, COUNT(C.Id) AS Customers 
  FROM Customer C
 GROUP BY C.Country

Note: Customers is a column alias, and C is a table alias.
Table aliases are commonly single capitalized characters.

Result:  21 records
Country Customers
Argentina 3
Austria 2
Belgium 2
Brazil 9
Canada 3

SELECT with ORDER BY, GROUP BY, Aliases

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the total amount spent by customer with easy to read column headers.
SELECT C.Id , C.LastName + ', ' + C.FirstName AS Customer, 
       SUM(O.TotalAmount) AS 'Total Spent'
  FROM [Order] O 
  JOIN Customer C ON O.CustomerId = C.Id
 GROUP BY C.Id, C.LastName + ', ' + C.FirstName
 ORDER BY SUM(O.TotalAmount) DESC

Table aliases simplify writing JOIN and ORDER BY clauses.
The C alias in C.Id helps identify the Customer Id versus the Order Id.

Results: 89 records
Id Customer Total Spent
63 Kloss, Horst 117483.39
71 Pavarotti, Jose 115673.39
20 Mendel, Roland 113236.68
37 McKenna, Patricia 57317.39
65 Wilson, Paula 52245.90

You may also like



Guides


vsn 3.1