How to Use Aliases in SQL Queries

An SQL alias is useful for simplifying your queries and making the query and its result more readable. This article explains why and how you can use aliases in your SQL queries.

You can temporarily rename a table or a column by giving it another name. This is known as an SQL alias. It’s a temporary change that does not affect the actual table name in the database. A temporary table name can also be called a correlation name.

Aliases are a feature of SQL that is supported by most, if not all, relational database management systems. If you are very new to SQL, consider taking our SQL Basics course, which also covers the topic of aliases.

Why Use an Alias in SQL?

Here are several reasons why you might consider using an SQL alias:

• Complex column and table names can be simplified or given a more suitable name. This makes the SQL query clearer and easier to understand.
• Aliases are useful when we are working with JOIN operations or aggregate functions like COUNT() and SUM().
• An SQL alias is also handy when using a self-join.
• When working with multiple tables, it is a good practice to prefix all column names with an alias so you can easily see which column belongs to which table.

By the way, aliases are just one term that SQL users should know. Other important SQL terms are detailed in this article, SQL Terms Beginners Should Know.

Now, let’s take a closer look at using aliases, starting with columns.

Renaming Columns Using Aliases

An alias can be used to rename the columns in your SQL query. The syntax is as follows:

SELECT 
  column_1 AS alias_1, 
  column_2 AS alias_2, 
  …
FROM table_name;

Let’s apply this to a practical example. We’ll use the following table, which contains basic information about our customers:

First, we’ll use a query that does not have aliases for the column names. Imagine we wanted to see which customers are under the age of 60. We could write the following query:

SELECT
  customer_id,
  customer_name, 
  age 
FROM customers 
WHERE age < 60

Executing this query will return this result set:

Now let’s introduce our column aliases. The query below now contains an alias for two columns:

SELECT
  customer_id AS id,
  customer_name AS name, 
  age 
FROM customers 
WHERE age < 60

Compare this with the result from our previous example. Pay special attention to the column names and you will notice the impact of our aliases. The column customer_id now appears as id. The column customer_name is shown as name. The column age is unchanged. This is our desired result!

Note that the AS keyword is entirely optional when using aliases. For example, these two SQL queries perform the exact same function:

SELECT 
  customer_id AS id, 
  customer_name AS name, 
  age 
FROM customers 
WHERE age < 60

SELECT 
  customer_id id, 
  customer_name name, 
  age 
FROM customers 
WHERE age < 60

If the alias you use contains spaces, you must put quotes around the alias. For example:

SELECT customer_name AS 'Customer Name'
FROM customers

That concludes using an SQL alias to rename columns. Next up, using an alias to rename tables!

Renaming Tables Using SQL Aliases

An alias can be used to rename the tables that are used in your SQL query. The syntax of table aliases is:

SELECT 
  column_1, 
  column_2, 
  …
FROM table_name AS alias_name;

Let’s apply this syntax to an example. We’ll use the same table:

customers

SELECT 
  customer_name, 
  age 
FROM customers AS c 
WHERE c.age < 60

Executing this query results in the following data being returned:

If you’re using a table name before a column name in the WHERE clause, you can't use the original table name; you must use the table alias. Also, when you would like to specify the table in the SELECT list, you will have to use the alias (e.g. c.customer_name). Again, the AS keyword is entirely optional. The query will work the same without it.

Using an Alias with a Self-Join

A self-join allows you to join a table to itself. This is useful when querying hierarchical data or comparing rows within the same table. Essentially, you treat the table as two (or more) separate tables by assigning aliases. If you’re not familiar with self-joins, see this article with seven examples of self-joining tables in SQL.

When self-joining a table, you can use a LEFT JOIN or an INNER JOIN. When using a self-join, it is important to use a logical SQL alias for each table. (Aliases are also useful for subqueries. An example of this is shown in this article that fully explains subqueries.)

Let’s look at how we can write a query that will JOIN a table to itself. For our example, we will use the table employee, which shows all our employees, their department IDs, and their managers’ IDs.

Say we want a result set that only shows an employee and their manager. How can we accomplish this? This can easily be done through the clever use of aliases in combination with a self-join. We will first try using a LEFT JOIN. Look at the code snippet below:

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

When joining the tables, we make sure that the value in the id column from the manager's table (employee m) equals the value in the manager_id column from the employee's table (employee e). By using LEFT JOIN instead of JOIN, we'll see all employees, even those who don't have a manager.

Watch out for the ambiguous column error; it can easily occur if you are not careful when writing a self-join query. To avoid this error, you must explicitly specify the table to which the column belongs (employee you named e or employee you named m). In this example, the first column is from table e (thus e.employee_name) and the second column is from table m (thus 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 John Walker. However, you can see that the manager for Mary Smith is Penelope Cruz. Notice the NULL value under the Manager column for John Walker. This is because John Walker has no manager; he is the boss. We were able to include him in our results because we used LEFT JOIN.

Let's tweak the query slightly and use an INNER JOIN:

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

The only major difference is the absence of John Walker from the Employee column. This is because the manager_id value for him was NULL, and INNER JOIN only returns matching columns – NULL values not being included.

Now you can perform self-joins, which can be applied to many different use cases. If you feel like you need to see more examples of self-joins to fully understand the topic, check out this blog post on how to join the same table twice!

Write Better SQL Queries Using Aliases

In this article, we’ve demonstrated the value of SQL aliases. They help you display clearer data – and have the added benefit of hiding potentially sensitive table or column names. If you enjoyed this article and want to continue learning about SQL, consider our SQL Fundamentals learning track. It will teach you basic SQL statements like WHERE, GROUP BY, ORDER BY, and HAVING. You’ll also learn how to JOIN tables and add, modify, or remove data from a database.