SQL Aliases: A Complete Guide

An SQL alias is a reference name for a table or a column. They are used to shorten your queries if you have long table names. Both table and column aliases are defined using the AS keyword. An alias lasts until a query has executed. Aliases are not permanent.

Have you ever become tired of writing out long column or table names in a complex query? It happens as you learn more about SQL. This is where aliases come to the rescue. An alias will help you shorten your query.

In this guide, we talk about what SQL aliases are and why they are used. We walk through two scenarios of an alias in action to help you understand how to use SQL aliases. Without further ado, let’s begin!

Note: This tutorial has been written using PostgreSQL.

What is an SQL Alias?

An SQL alias is like human a human alias. The SQL alias assigns a placeholder name to a table or a column in a table. Aliases are useful because they help make queries and their results more readable and easier to interpret.

A good alias is one that can be understood as quickly as possible.

The alias n would be confusing to describe a column called name if there was also a column called number_of_products. Both start with the letter n.

fn may be more appropriate because it takes the first letter of each word in full name. This alias is less likely to cause confusion.

Remember that your SQL queries may be used by someone else, not just yourself. You should make every effort to make your queries as intuitive as possible for all readers.

SQL Aliases: Columns

You use aliases on column names. Column name aliases lets you change the name of a column in the output of a query.

Let’s take a look at two tables in our example database. These databases are called employees and branches.

The employees table has four columns and four rows. The branches table has two columns and three rows.

Let’s write an SQL SELECT statement that tells us the highest salary earned at each branch:

SELECT branches.branch_name, MAX(employees.salary)
FROM employees LEFT JOIN branches
ON employees.branch_id = branches.branch_id
GROUP BY branches.branch_name;

We use the MAX() SQL aggregate function to select the highest value in the “salary” column of the “employees” table. We use an SQLLEFT JOIN statement to connect the “branches” and “employees” tables together. This lets us see the return of each branch.

Let’s see what our query returns:

Our query works. The title names of our query are not very intuitive. For someone who does not write this, query “max” may be confusing.

We solve this by specifying column alias names:

SELECT branches.branch_name AS "Branch Name", MAX(employees.salary) as "Highest Salary"
FROM employees LEFT JOIN branches
ON employees.branch_id = branches.branch_id
GROUP BY branches.branch_name;

We specify two aliases. “branch_name” gets the alias “Branch Name”. Our MAX() aggregate function gets the alias “Highest Salary”. Let’s run our query again:

The returning rows by our query are the same. The table headings change. Now, “branch_name” is “Branch Name” and “max” is “Highest Salary”.

SQL Aliases: Tables

You can define an SQL alias on a table. This is useful if you are going to refer to a table many times in a query. Such an event often happens in more complex queries.

When you query multiple tables, you need to specify the name of each table before the name of the columns. The table and column name are separated with a period (.). This ensures SQL knows exactly which table each statement refers to in your query.

While this feature protects you from making your queries ambiguous, it makes them less readable. Thus, you should make sure your aliases are not too difficult to understand.

Let’s write a query that returns the name of every employee alongside the name of the branch at which they work:

SELECT employees.name, branches.branch_name
FROM employees LEFT JOIN branches
ON employees.branch_id = branches.branch_id;

Our query returns:

We query two tables: employees and branches. We use a LEFT JOIN to connect the value of “branch_id” in both tables. This allows us to see the names of each branch with which an employee is associated.

Our query is quite long. We repeat the words “employees” and “branches” twice. In a longer query, we have to use these words even more.

We use aliases to make our query shorter. We need to use the AS keyword to create a table name as an alias in an SQL query:

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match

SELECT e.name, b.branch_name
FROM employees AS e LEFT JOIN branches AS b
ON e.branch_id = b.branch_id;

This query returns the same output as our first query.

We assign two aliases:

• “employees” is “em”
• “branches” is “br”

Any time we refer to em or br in our query, SQL treats it as if we are referring to “employees” and “branches”, respectively. Our table aliases allow us to reduce the amount of repetition in our SQL statement.

Conclusion

An SQL alias is a substitute name for a column or table. Aliases are query-specific. This means that an alias in one query cannot be used in another query without redefining the alias. An alias is set using the AS keyword.

Are you interested in learning more about SQL? Check out our How to Learn SQL guide. This comprehensive guide covers everything from what courses you should consider to helpful online SQL resources.