Querying with Aliases

When writing larger queries which return more fields or join together an increasing number of tables it can be easy for scripts to get unruly. A fundamental skill to help with the readablity of you queries is through the use of Aliases.

An alias provides us a was to change how we reference part of our query and comes in a couple of favours we’ll cover below.

Table aliases

The first type of alias we’ll look at is the Table Alias. This lets us refer to a table by another name, typically a short or abbreviated version of its full name.

This is done by providing the alias for the table following it’s definition in the query. Let’s look at a quick example for reference:

SELECT
    o.OrderId,
    o.OrderValue;
    ol.ProductCode,
    ol.Quantity
FROM
    dbo.Orders o
    INNER JOIN dbo.OrderLines ol ON o.OrderId = ol.OrderId
WHERE
    o.CustomerId = 123;

Here you’ll see we’re using the aliases o and ol on our tables as stated in the FROM clause. Now, instead of needing to prefix fields with the full table name (e.g. Orders.OrderId) we can use the alias to indicate which table we’re referring to.

When using aliases for the table name we want to make sure that the alias relates to the original table name in some way. In this instance we’ve changed Orders to o and OrderLines to ol as they’re the first letters of each word. This should be done as opposed to using sequential aliases such as t1, t2, t2, or a, b, c which don’t have any context and conversely make it much harder to read queries.

Using table aliases can greatly help with understanding for a query as it makes the code much more succinct whether that be for someone reading it, or someone who needs to maintain it.

Column Aliases

A Column Alias is similar to the table one we’ve just looked at however it covers the columns which are being returned from a query. Using these allows us to change the name of the column being returned to something other than what is stored in the database.

Examples for where this could be used are if we wanted to make the field names return consistently regardless of how they’re stored (e.g. boolean value columns to always be prefixed with Is), or where we want to return the same column name from different tables with different context (e.g. an Id field present in multiple tables).

There are multiple ways to alias a column which can be see in the example below:

SELECT
    o.id OrderId,
    c.id AS CustomerId,
    OrderValue = o.Value
FROM
    dbo.Orders o
WHERE
    o.id = 456;

You’ll see the following approaches have been used to alias the columns:

• o.id OrderId – here we simply provide the alias after the field name
• c.id AS CustomerId – this is the same as above however we’re explicitly telling the engine that we want to provide the Id field AS a field named CustomerId
• OrderValue = o.Value – in this instance we start with the column alias and then state where that field should be populated from

All of these are equivalent and will result in aliasing the columns in the same way as each other, it’s simply down to personal preference on which approach you might take.

Personally I like the third option for clarity as queries will have all the aliases aligned under the SELECT so it can be easier to identify the field you may be looking for and then see the definition next to it.

Wrap up

Aliasing allows us to tailor our queries in ways to make them more readable either in terms of how the query is structured (with table aliases) or how the data is output (with column aliases). These are fundamental concepts in query design and will quickly become necessary as scripts grow larger and more complex to follow.

Although a basic concept, aliasing is something which I see applied inconsistently or in ways which don’t support readability. I’d look to find a style which fits yourself or your team and try to adhere to it whenever possible to make your queries as easy to approach and maintain as possible in the future.