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.
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.
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 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
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
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.
Country | Customers |
---|---|
Argentina | 3 |
Austria | 2 |
Belgium | 2 |
Brazil | 9 |
Canada | 3 |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
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.
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 |