Aliases in SQL – Little helpers for better SQL

The language SQL (Structured Query Language) has a vast functionality and vocabulary. Many struggle with learning the basics of INSERT, SELECT, UPDATE and DELETE already. If you managed that, there are many less used other nuances of the language to discover and master.

It’s no surprise that beginners in SQL overlook a small aspect of the SQL syntax that seems not to be essential at first. Experts in the language know these little helpers very well and use them all the time, but frequently forget to explain them to beginners, because once mastered, they seem to be so small and trivial.

Today I want to improve this situation and I’m going to introduce you to Aliases in the SQL programming language.

How to define an Alias

Aliases are “replacement names” for the columns and tables you use in your SQL statements. They are very simple to create and use. The keyword to create an Alias is AS. You use the AS keyword to define a new name for a column or a table in your SQL statement.

Strictly speaking, the keyword AS is optional in some circumstances. Still I recommend you always use it while you are beginning to use Aliases to make it more obvious what is going on.

Column Aliases

SELECT ColumnWithACrazyWeirdVeryLongName AS SimpleName FROM tblYourTable;

In the results of this query the data from the table column ColumnWithACrazyWeirdVeryLongName will now be displayed in the output column SimpleName.

The Alias in the above example is handy, but you might rightfully argue that is of minor usefulness, because

There are lots of situations where you can use Aliases to make your SQL Statements simpler to write and easier to read. However, there are some scenarios as well, where you have to use Aliases to be able to write a certain query at all.

The situation is different, if you output the results of a calculation from your query. As the output column does not have a name, you need to define one for it. And once again you do that by using an Alias.

SELECT ItemPrice * Quantity AS ExtendedPrice FROM tblOrders;

In this case you need to use the Alias. Otherwise you are going to have problem processing the results. Some database systems, e.g. Microsoft Access technically handle the problem automatically for you by automatically creating an Alias for you. But that will have an ugly name such as Expr1 or Ausdr1, which has no meaning and is bound to create confusion if you have got multiple calculated columns in your query. - So do use the Alias here!

Table name Aliases

You can create Aliases for table names as well. For simple queries querying only one table this does not make much sense, but as soon as you use more than one table, it is convenient to work with Aliases for the table name.

Improved readability

SELECT tblCustomer.CustomerName, tblAddress.Street, tblAddress.City, tblAddress.ZIP, tblServiceAppointment.AppointmentDate, tblServiceAppointment.Description FROM tblCustomer INNER JOIN tblAddress tblCustomer.AddressId = tblAddress.AddressId INNER JOIN tblServiceAppointment ON tblCustomer.CustomerId = tblServiceAppointment.CustomerId;

SELECT c.CustomerName, a.Street, a.City, a.ZIP, sa.AppointmentDate, sa.Description FROM tblCustomer AS c INNER JOIN tblAddress AS a c.AddressId = a.AddressId INNER JOIN tblServiceAppointment AS sa ON c.CustomerId = sa.CustomerId;

Which one is more readable? - I strongly opt for the second one with the Aliases.

It is much less effort to write it as well and if you happen to rename a table, you just need to replace the table name once in the whole statement. Convenient, isn’t it?

Required Aliases

In the above example it was still optional to use the Aliases, but it might happen, you absolutely have to use them. Let’s extend that sample a little bit.

SELECT c.CustomerName, ca.Street AS CustomerStreet, ca.City AS CustomerCity, ca.ZIP AS CustomerZIP, sa.AppointmentDate, sa.Description, st.TechnicianName, ta.Street AS TechnicianStreet, ta.City AS TechnicianCity, ta.ZIP AS TechnicianZIP FROM tblCustomer AS c INNER JOIN tblAddress AS ca c.AddressId = ca.AddressId INNER JOIN tblServiceAppointment AS sa ON c.CustomerId = sa.CustomerId INNER JOIN tblServiceTechnician st ON sa.TechnicianId = st.TechnicianId INNER JOIN tblAddress ta ON st.AddressId = ta.AddressId;

Do you see what going on there? We have got a general tblAddress table in our database containing all address. Now we want to query the customer address and the service technician’s address in one query. We have to add the tblAddress twice and we have to output the address tables’ columns twice to get the desired results.

Writing a query that references any one table twice would be impossible, because there would be no way to tell them apart; they have the same name. By adding an Alias immediate after the table name, each of the table’s instances gets its own unique identity within the query.

By using aliases for the table names as well as for the output columns of the address table this query is still pretty readable and clear. – Without Aliases you would be utterly lost here.

Limitations of Aliases - Scope

Aliases are very helpful, but I have to acknowledge their power is limited. The main limitation is their scope. Aliases declare a new name of a table or column visible to the outside world of their declaration.

It might not be obvious immediately, but SQL Statements are hierarchical. In the SELECT-part of a query (the output column list), you can only reference tables (or other objects) that are included in the underlying FROM-Clause. If you define any Alias in the FROM clause it will be available everywhere else in the statement, because all of it is based on the FROM clause.

If you define an Alias in the Select list of a query, this Alias will be visible only to the outside of that level of the SQL-Statement. As the Select list is the outermost part of your Select-statement, any Alias define there will not be visible anywhere within the query.

SELECT ItemPrice * Quantity AS ExtendedPrice FROM tblOrders;

The Alias ExtendedPrice will be seen outside of that statement, like from the viewpoint of any client code executing that query. It will not be visible anywhere inside the statement. You cannot reference it in any further calculation and you cannot reference it in the WHERE condition or in the ORDER BY clause.

SELECT ItemPrice * Quantity AS ExtendedPrice, ~~ExtendedPrice~~ * TaxFactor AS TotalPrice FROM tblOrders ORDER BY ~~ExtendedPrice~~ DESC;

As inconvenient as it may be, to achieve the results intended by the above statement, you need to repeat the original expression in both locations.

SELECT ItemPrice * Quantity AS ExtendedPrice, (ItemPrice * Quantity) * TaxFactor AS TotalPrice FROM tblOrders ORDER BY (ItemPrice * Quantity) DESC;

Dangers of Aliases

Due to their limited power, there is not too much danger involved in the use of Aliases.

I would like to draw your attention to two situations, where you need to be careful with Aliases to avoid undesired results.

Overriding existing column names

You can use an Alias to override an existing column name. This can be used to change application logic at a very low database level, without touching the layers above.

Assuming there is a stored Query (View, Function, or …) that is consistently used by all parts of an application, you can change the data there without modifying any of the upper layers.

SELECT WidgetPrice * 1.1 AS WidgetPrice FROM tblWidget;

Use this with great care though, because unless you really look at the SQL of that query, it will not be apparent that it does not output the original WidgetPrice from the table.

Uniqueness of Aliases and Names

With most DBMS, it is possible to use the name of an existing column in the output list as an Alias for another expression. And with some it is even possible to use one and the same Alias multiple times.

SELECT c.CustomerName, ca.Street, ca.City, ca.ZIP + ' ' + ca.City AS City, somethingcompletelydifferent AS City FROM tblCustomer c INNER JOIN tblAddress ca c.AddressId = ca.AddressId;

Many DBMS will execute this statement without an error. What exactly will happen, depends on the DBMS involved, but I assure you, there will be nothing good coming from this.

So you need to take care yourself that you do not accidentally use a term as an Alias that is already used in the statement.

Conclusion

Aliases are easy to understand and to use. They are tremendously helpful to write SQL Statements that are simpler to write, better to read and easier to maintain.

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

Benefits of the newsletter subscription

Aliases in SQL – Essential knowledge about little helpers