How to Use Aliases with SQL JOINs

SQL aliases are custom names that you can give to the columns and tables you include in your queries. Aliases are very useful, as they can greatly improve the readability and maintainability of your query. We’ll be taking a closer look at using SQL aliases with JOIN and why you might need to do so.

The best way to master aliases in SQL is practice. I recommend our interactive SQL Practice Set course. It contains 80+ exercises for different SQL topics, starting with simple one-table queries to complex multi-table aggregations and subqueries.

There are several types of aliases you can use in SQL:

1. Aliases for tables.
2. Aliases for table columns.
3. Aliases for subqueries.

Although there are several different ways of using aliases in your queries, the syntax is similar for all of these methods. Let’s look at the syntax of aliases.

SQL Alias Syntax

The syntax shown below covers both column and table aliases:

SELECT
  t1.column_name (AS) new_column_name,
  t2.column_name (AS) other_new_column_name,
  ...
FROM table1 (AS) t1
JOIN table2 (AS) t2
  ON t1.column_name = t2.column_name
…

If you feel overwhelmed by this syntax, consider getting to grips with the basics of SQL queries by enrolling in our interactive SQL Basics course. It contains over 120 hands-on SQL exercises. It will provide you with a solid practical foundation of SQL. The exercises are based on real-world problems you'll solve daily in your work.

SQL Column Aliases

First, let’s look at the column aliases:

 t1.column_name (AS) new_column_name,
 t2.column_name (AS) other_new_column_name

The AS keyword here is optional. You can include it if you want, but the query will behave exactly the same regardless.

The alias is defined directly after the column name (or the AS keyword). In this case, our aliases are new_column_name and other_new_column_name.

If you want to include spaces in your new column name, enclose it in quotes. We could update this example to:

SELECT t1.column_name (AS) 'new column name'

Excluding the single quotation marks here would cause an error.

Ok, that covers SQL aliases for column names. Let’s look at table aliases.

SQL Table Aliases

We define table aliases like so:

FROM table1 (AS) t1
JOIN table2 (AS) t2

As before, the inclusion of the AS keyword is completely optional. We declare the alias for table1 to be t1. Similarly, the alias for table2 is t2.

When you rename database tables, you must use the table aliases in the ON condition, in the SELECT list when selecting the columns (if there's a need to specify the table for the column), and in other parts of the query.

You can see we follow this rule by including t1 and t2 in our SELECT clause:

SELECT
  t1.column_name (AS) new_column_name,
  t2.column_name (AS) other_new_column_name,
  ... 

SQL Alias for Subqueries

An SQL subquery is a query inside another query. Let’s look at the syntax of how to use aliases in a subquery.

SELECT column_1, 
       column_2 = (SELECT COUNT(t2.id)
                         FROM table_2 t2
                        WHERE t2.id = t1.id)
  FROM table_1 t1

The subquery is the part of the query in bold type. You can see how aliases help us access the correct table at each part of the query.

That concludes our look at SQL alias syntax. Let’s see some practical examples of using aliases with JOIN!

Examples of Using SQL Alias with JOIN

In this section, we’ll look at some common alias use cases and explain how to implement each one. SQL JOINs are a tricky topic – they’re included in this article about SQL Topics That Require Practice. The more exposure to practical examples you get, the better off you will be.

Using an Alias with Non-Unique Column Names

When you’re dealing with multiple tables, it’s common to have the same column name in both tables. This frequently occurs with the id column. Here’s an example that demonstrates this:

SELECT employee.id, customer.id, employee_name, employee_department 
FROM employees 
INNER JOIN customer 
ON employee.id = customer.employee_id 

The resulting data is displayed as:

This result will confuse stakeholders, as they have no way of telling which id belongs to which table. Introducing a column alias for each id column can help:

SELECT employee.id employee_id, customer.id customer_id, employee_name, employee_department 
FROM employees 
INNER JOIN customer 
ON employee.id = customer.employee_id 

Now we get a much more understandable result:

Improve Query Readability

An alias can simplify and improve the readability of your query. For example, imagine there was a column called cn_index that stored customer names. It’s not immediately obvious what this column represents; you would need the data model’s documentation or someone explaining it to you to decipher its meaning. This problem can be reduced by using an alias in any query that includes this column. This might look like:

SELECT cn_index AS customer_id 
FROM customers

The same goes for any badly named tables you encounter in your database.

Improve Query Maintainability

An alias also helps with maintainability. Imagine you had a query like the one below:

SELECT customer.id, customer.name, customer.age, client.id 
FROM customer 
JOIN client 
ON customer.id = client.id

Now imagine there was a change to our customer table, like being renamed to customers_europe. You would have to make the change everywhere this table is referenced:

SELECT customers_europe.id, customers_europe.name, customers_europe.age, client.id
FROM customers_europe
JOIN client 
ON customers_europe.id = client.id

This may not seem like a big step, but this is a short, simple query. Imagine you had to update multiple stored procedures and functions because of this change.

Let’s see how aliases help with this issue. Our first query, when updated to use an alias, might look like this:

SELECT c.id, c.name, c.age, client.id
FROM customer c
JOIN client
ON c.id = client.id

Now when we go to update our table, we only need to make the change in one place:

SELECT c.id, c.name, c.age, client.id
FROM customers_europe c
JOIN client
ON c.id = client.id

This results in massive time savings if you have to update a large number of existing queries, functions, and stored procedures.

Self-Joins

A self-join allows you to join a table to itself. It’s useful for querying hierarchical data or comparing rows within the same table. When self-joining a table, you can use a LEFT JOIN or an INNER JOIN. When performing a self-join, it is important to use a logical SQL alias for each instance of the table.

Let’s look at how we can write a query that will join a table to itself. For our example, we will use the following table, tbl_employee. It shows all of the employees in the company, the ID of their department, and the ID of their manager:

Say we want a result set that only shows an employee and their manager. How can we accomplish this? By using aliases in combination with a self-join. Look at the code snippet below:

SELECT e.employee_name AS 'Employee',
m.employee_name AS 'Manager'
FROM tbl_employee e
LEFT JOIN tbl_employee m
ON m.id = e.manager_id

Note that we’ve used LEFT JOIN as our self-join. When you’re joining tables, watch out for the ambiguous column error, which can easily occur if you’re not careful. To avoid this error, you must explicitly specify the table alias in the column name (e.employee_name and m.employee_name).

Executing the above query yields the following result set:

There’s our desired result! You can clearly see each employee and their manager. Most employees report to Mr. Burns. However, you can see that the manager for Frank Grimes is Homer Simpson. Notice the NULL value under the Manager column for Montgomery Burns. This is because Montgomery Burns has no manager; he’s the boss.

Let's tweak the query slightly and use an INNER JOIN this time for our self-join: 

SELECT e.employee_name AS 'Employee',
m.employee_name AS 'Manager'
FROM tbl_employee e
INNER JOIN tbl_employee m
ON m.id = e.manager_id

The only major difference is the absence of Montgomery Burns from the Employee column. This is because the manager_id value for him was NULL and INNER JOIN only returns matching columns; NULL values are not included.

Now you can perform self-joins, which are applicable to many different use cases. If you want to see more examples, check out this excellent illustrated guide to self-joins. The self-join is also featured in this useful SQL JOIN cheat sheet. Bookmark the page or download it and you will always have a quick guide to the different JOIN types.

Create Meaningful Results

When you use aggregate functions, by default the result will be shown in a column with no name. An alias is a perfect way to add meaning to the results of your aggregate functions.

Let’s look at a brief example to see how it works. Imagine we have a table called payments that contains some payment data:

Here is a query:

SELECT SUM(payment_amount)
FROM payments

Executing this query yields the following result:

We have no idea what this result represents because it has no column name. Let’s update our query with an alias to make the results more meaningful.

SELECT SUM(payment_amount) AS 'Sum of payments'
FROM payments

Execute the query to see our new and improved query results:

These were just some uses of the SQL alias. For more examples, check out this blog post on how to use aliases in SQL.

SQL Alias JOIN Explained

We’ve covered how an alias can help with the readability and maintainability of your SQL query, not to mention the integral role it plays in self-joins. We’ve looked at many different use cases and examples. To get a more complete education on the topic,  check out the LearnSQL.com SQL JOINs course.

If you’d like to learn more than just JOINs – maybe you’re seeking a complete SQL education  – the SQL from A to Z learning track would be a better option for you. Upon completing the learning track, you will possess the necessary tools (including JOINs and aliases) to freely and efficiently work with any type of data.