Programming Languages - SQL - Tech tutorial

The power of a SQL alias.

What is a SQL alias?

In SQL, an alias is a temporary name given to a table, column, or expression in a query. It is one of the easiest and most common practices used in SQL writing due to its simplicity of use and the meaningful impact it can have on your query. Once you understand aliasing, this practice will become a staple in your SQL toolkit.

Aliasing has been a part of the SQL language since its creation and is a usable syntax across all SQL dialects such as MySQL, PostgreSQL and SQL Server.

keyboard

How to use a SQL alias

Using an alias relies on the AS clause in your query. When you alias an object, you simply name it “AS” something else.

In the below example, you can see an alias in action. This query has aliased the uid column AS user_id and aliased the table name user_table AS a.

code1

When to use a SQL alias

A SQL alias is used when you want to make your code cleaner and easier to read and write . An alias provides a shorthand name for a longer or more complex object name like a table or column. It is a quick way to simplify query writing by creating custom names for these data objects to improve interpretation. 

An alias can be as simple as an abbreviation of a current name or a brand new one you’ve created in order to improve the readability of your code.

For example, a table called customers_and_revenue_q3_2022 table. 

This table has a long name which can be a hassle to read and write out in your code. The table name can be aliased to save time.

Without an alias:

code2

With an alias:

code3

The two queries deliver identical results when executed, however, the query using an alias for the table name is much cleaner and easier to read (and you’ve saved time on writing it out!).

Where can a SQL alias come in handy?

A SQL alias can be used in many scenarios where the goal is to optimize and simplify your code. 

Some example scenarios that leverage the power of this practice are:

Joining tables

When joining on 2 or more tables, aliasing the table names can make selecting columns clearer and more efficient. By giving the tables unique aliases, it helps to identify which data is being sourced from where without long and cumbersome naming conventions.

In the below example, we’ve aliased the user_table AS a and the revenue_table AS b, making it cleaner to query the individual columns in the SELECT statement

code4

Using aliases for our table names has made this query much more readable by cleaning up lengthy naming conventions. 

To avoid naming conflicts

It is not uncommon to have multiple tables with the same column names. Using a SQL alias in this scenario can help easily and uniquely label the data source when referencing similar columns from different tables.

Take the example below. 

Two tables, user_table_q3 and user_table_q4 contain the same column names, but different values as one dataset references Q3 and one references Q4. When querying these, it is helpful to alias columns with shared names in order to differentiate the datasource in the output. 

code5

We’ve aliased the city columns from both tables in order to differentiate which city and quarter we are viewing in our output. Without aliasing, we would not have been able to differentiate these 2 columns in our output as they have identical names. Now there are 2 uniquely named columns- one for user_city_q3 and one for user_city_q4.

When object names aren’t intuitive

Oftentimes in SQL, there are tables and columns that have less than optimal names. Assigning an alias to a poorly named data object helps to give a better understanding of the data being queried.

In the below example, we can see some messy names in the user_table_q42022. The objects have untidy names and can lead to a confusing query. If we alias the table and columns, there will be a much cleaner output.

code6

Now instead of an output column named  uid_q4_2022, we’ve renamed it to simply user_id, and have cleaned up the location and group_name column names as well. Lastly, instead of referencing the table name each time, we’ve also aliased that to make the query easier and cleaner to read and write.

Aggregate functions

When using aggregate functions like COUNT, SUM, or AVG in your query, it is useful- and even considered best practice-  to use an alias to rename the resulting output column.

Below, we can see that when pulling average quarterly clicks, we want to assign an alias to the resulting column in order to name what we’ve just calculated.

code7

Subquerying

Using a SQL alias becomes especially important when subquerying, as the resulting temporary dataset needs a name. Aliasing can provide clarity in your subqueries by making it easier to follow which columns are being referenced and which tables are being joined. It helps to avoid ambiguity by clearly identifying which subquery each column belongs to. Aliasing also helps to save time by creating simple referencing for a dataset that may be complex. 

For example, let’s say we wanted to see how many users placed >= 5 orders so far in 2023. We’d want to create a subquery in order to count the number of orders and filter from that data.

code8

We’ve aliased our subquery as “b” and can query from that result while adding our necessary filter of “>=5”

Overall, using a SQL alias is one of the most adoptable practices to apply to your queries to make querying easier, more efficient and less error prone. As you become more comfortable with the language, you’ll wonder how you ever coded without them. 

Looking to expand your knowledge of SQL? Take our SQL online Nanodegree program to gain in-demand skills and master the core programming language for data analysis.