sql

SQL Alias Example

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.

SQL Alias

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

Download

You can download the full source code of this example here.

SQL Alias Example

Kevin Anderson

Kevin has been tinkering with computers for longer than he cares to remember.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button