This article illustrates the use of SQL Alias through examples. The code was prepared with MySql 5.7 syntax, most notable for its use of backticks (`) for identifier quoting, but should be easily adaptable to other SQL dialects as well.
1. What is an alias?
An alias is a programmer-assigned name for a column, table, or view in an SQL DML (Data Manipulation Language) statement. Aliases may be required in order to give new and distinct names to similarly-named tables or columns in a query or subquery. But they can also be used, voluntarily and if desired, to improve both the readability of queries and the appearance of their results.
An alias is placed after a column expression or table reference, optionally introduced by the keyword AS.
2. Do I need aliases?
ANSWER: No, not always. While there are good reasons you might want to use aliases anyway, many (if not most) queries can be formulated successfully without them.
Here’s a query that produces a list of all item orders, along with customer and items details and an extended cost (item unit price times quantity ordered):
ex1.sql
SELECT
Orders.id,
Customers.id,
Customers.name,
Items.id,
Items.description,
Items.price,
OrderItems.quantity,
OrderItems.quantity * Items.price
FROM
Orders
JOIN Customers ON Customers.id = Orders.customerId
JOIN OrderItems ON OrderItems.orderId = Orders.id
JOIN Items ON items.id = orderItems.itemId
ORDER BY
Orders.id
Here’s a sample of the output from this query, as produced by the mysql command-line client:
ex1 Result
+----+----+------------------+-----+----------------------------------+-------+----------+-----------------------------------+
| id | id | name | id | description | price | quantity | OrderItems.quantity * Items.price |
+----+----+------------------+-----+----------------------------------+-------+----------+-----------------------------------+
| 1 | 13 | Brenda Quaid | 3 | Gewgaw, large, black | 47.33 | 68 | 3218.44 |
| 1 | 13 | Brenda Quaid | 65 | Thingamajig, medium, black | 27.62 | 25 | 690.50 |
| 2 | 19 | Debbie Xavier | 47 | Gadget, large, red | 27.43 | 6 | 164.58 |
| 2 | 19 | Debbie Xavier | 24 | Gizmo, medium, white | 32.99 | 16 | 527.84 |
| 2 | 19 | Debbie Xavier | 13 | Doohickey, large, yellow | 30.35 | 16 | 485.60 |
| 3 | 24 | Patricia Jackson | 7 | Doohickey, medium, yellow | 75.23 | 40 | 3009.20 |
| 3 | 24 | Patricia Jackson | 36 | Whatchamacallit, small, orange | 21.44 | 42 | 900.48 |
3. If I don’t need aliases, why might I want them anyway?
ANSWER: One good reason is that they can help make your queries and their results prettier.
The query presented in the previous section did its job more or less adequately, but there is some room for improvement:
- The column headings are in all lowercase, whereas Title Case would look better;
- There are three columns entitled “id“, a likely source of confusion for anyone trying to read the report;
- The heading “OrderItems.quantity * Items.price” for the extended price, while technically correct, is a bit unwieldy, and widens the column unnecessarily;
- The query itself is a bit verbose and perhaps less readable than it could be.
The first three issues can be addressed by assigning aliases to the result columns: our query tool will then use these aliases as column headings in its output. And the wordiness of the query can be reduced by assigning shorter aliases for the tables and using those aliases in place of the table names to qualify column references.
Here’s the rewritten query, looking a bit less cluttered and, hopefully, more readable:
ex2.sq2
SELECT
o.id AS `Order#`,
c.id AS `CustID`,
c.name AS `Cust. Name`,
i.id AS `Item#`,
i.description AS `Item Description`,
i.price AS `Price`,
oi.quantity AS `Quantity`,
oi.quantity * i.price AS `Ext. Price`
FROM
Orders AS o
JOIN Customers AS c ON c.id = o.customerId
JOIN OrderItems AS oi ON oi.orderId = o.id
JOIN Items AS i ON i.id = oi.itemId
ORDER BY
o.id
The report produced by our query tool (mysql, in this case) is much improved as well. As you can see, the extended price column is much more compact and all the column headings are more attractive because mysql used our column aliases for headings:
ex2 Result
+--------+--------+------------------+-------+----------------------------------+-------+----------+------------+
| Order# | CustID | Cust. Name | Item# | Item Description | Price | Quantity | Ext. Price |
+--------+--------+------------------+-------+----------------------------------+-------+----------+------------+
| 1 | 13 | Brenda Quaid | 3 | Gewgaw, large, black | 47.33 | 68 | 3218.44 |
| 1 | 13 | Brenda Quaid | 65 | Thingamajig, medium, black | 27.62 | 25 | 690.50 |
| 2 | 19 | Debbie Xavier | 47 | Gadget, large, red | 27.43 | 6 | 164.58 |
| 2 | 19 | Debbie Xavier | 24 | Gizmo, medium, white | 32.99 | 16 | 527.84 |
| 2 | 19 | Debbie Xavier | 13 | Doohickey, large, yellow | 30.35 | 16 | 485.60 |
| 3 | 24 | Patricia Jackson | 7 | Doohickey, medium, yellow | 75.23 | 40 | 3009.20 |
| 3 | 24 | Patricia Jackson | 36 | Whatchamacallit, small, orange | 21.44 | 42 | 900.48 |
4. When must I use aliases?
ANSWER: There are just a few situations where aliases are absolutely necessary:
- If you use the same database table (or view) multiple times in a query, you need to assign table aliases in order to distinguish the different occurrences;
- A derived table (i.e., a virtual table formed by a subquery) must always be given an alias (because it doesn’t have a “natural” name of its own);
- Columns of derived tables must have unique names; if two or more columns of a derived table naturally end up with the same name, you must assign those columns new and distinct names using aliases.
Here is an example of a query which joins the OrderItems table to itself in order to match each item order with any other other order for a greater quantity of the same item:
ex3.sql
SELECT
oi1.orderId AS `OrderID 1`,
oi1.itemId AS `ItemId 1`,
oi1.quantity AS `Quantity 1`,
oi2.orderId AS `OrderID 2`,
oi2.itemId AS `ItemId 2`,
oi2.quantity AS `Quantity 2`
FROM
OrderItems oi1
JOIN OrderItems oi2 ON
oi1.orderId <> oi2.orderId AND
oi2.itemId = oi1.itemId AND
oi2.quantity > oi1.quantity
ORDER BY
`ItemId 1`, `OrderId 1`
Notice that the two occurrences of OrderItems have been assigned aliases, so that “left” OrderItems (alias oi1) can be distinguished from “right” OrderItems (alias oi2) both in the ON condition and in the select list. We’ve also used column aliases to assign distinctive column headings: suffix “1” identifies data from “left” OrderItems, suffix “2” identifies data from “right” OrderItems.
Here’s a sample of the result:
ex3 Result
+-----------+----------+------------+-----------+----------+------------+
| OrderID 1 | ItemId 1 | Quantity 1 | OrderID 2 | ItemId 2 | Quantity 2 |
+-----------+----------+------------+-----------+----------+------------+
| 9 | 4 | 16 | 5 | 4 | 23 |
| 3 | 7 | 40 | 15 | 7 | 99 |
| 2 | 13 | 16 | 9 | 13 | 89 |
| 17 | 18 | 59 | 9 | 18 | 70 |
| 9 | 19 | 8 | 20 | 19 | 28 |
| 11 | 20 | 51 | 9 | 20 | 52 |
| 6 | 22 | 39 | 20 | 22 | 92 |
This next query finds the most expensive line item on each order. Here, OrderItems is again used twice: once in the FROM clause of the main query, and a second time in a subquery contained in an ALL quantifier:
ex7.sql
SELECT
oi.orderId AS `OrderId`,
oi.itemId AS `Item ID`,
i.description AS `Item Desc`,
oi.quantity AS `Qty`,
i.price AS `Item Price`,
i.price * oi.quantity AS `Ext. Price`
FROM
OrderItems oi
JOIN
Items i ON i.id = oi.itemId
WHERE
i.price * oi.quantity > ALL (
SELECT
i1.price * oi1.quantity
FROM
OrderItems oi1
JOIN Items i1 ON i1.id = oi1.itemId
WHERE
oi1.orderId = oi.orderId AND
oi1.itemId <> oi.itemId
)
The result:
ex7 Result
+---------+---------+-------------------------------+-----+------------+------------+
| OrderId | Item ID | Item Desc | Qty | Item Price | Ext. Price |
+---------+---------+-------------------------------+-----+------------+------------+
| 1 | 3 | Gewgaw, large, black | 68 | 47.33 | 3218.44 |
| 2 | 24 | Gizmo, medium, white | 16 | 32.99 | 527.84 |
| 3 | 7 | Doohickey, medium, yellow | 40 | 75.23 | 3009.20 |
| 5 | 4 | Widget, medium, white | 23 | 42.00 | 966.00 |
| 6 | 17 | Gizmo, X-large, blue | 37 | 74.27 | 2747.99 |
| 7 | 34 | Thingamajig, X-large, blue | 87 | 48.20 | 4193.40 |
| 8 | 50 | Thingamajig, medium, blue | 82 | 45.15 | 3702.30 |
5. Download the source code
