Skip to content

Alias

2007 March 1
tags:
by Shannon Lowder

Within a few days of using SQL, you’re going to start wondering if there is a way to speed up your code writing. The good news is you can do that with Aliases. You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table or column names.

An alias name could be anything, but usually it is short. I like using single character aliases if I can get away with them

SQL Alias Syntax for Tables

SELECT
  columnName
FROM tableName AS tn

SQL Alias Syntax for Columns

SELECT
  columnName AS cn
FROM table_name

The only catch is be careful when you have similarly aliased objects. If the two aliases introduce more confusion than clarity… Only alias one of them.

Alias Example

Assume we have a table called “Persons” and another table called “Product_Orders”. We will give the table aliases of “p” and “po” respectively.

Now we want to list all the orders that “Ash Williams” is responsible for. (I’m thinking boomsticks, shotguns, and hopefully the Necronomicon!

We use the following SELECT statement:

SELECT
    po.OrderID
  , p.LastName
  , p.FirstName
FROM Persons AS p
INNER JOIN ProductOrders AS po
	ON p.personID = po.personID
WHERE
  p.LastName='Williams'
  AND p.FirstName='Ash'

Compare the aliased version with the following:

SELECT
    ProductOrders.OrderID
  , Persons.LastName
  , Persons.FirstName
FROM Persons
INNER JOIN ProductOrders
	ON Persons.personID = productOrders.personID
WHERE
  Persons.LastName='Williams'
  AND Persons.FirstName='Ash'

As you’ll see from the two SELECT statements above; aliases can make queries easier to both write and to read. If you have any questions about aliases, or anything else in SQL, please let me know! I’m here to help!

No comments yet

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS