Alias (SQL)
Alias (с англ. — «псевдоним») — это имя, назначенное источнику данных в запросе при использовании выражения в качестве источника данных или для упрощения ввода и прочтения инструкции SQL. Такая возможность полезна, если имя источника данных слишком длинное или его трудно вводить. Псевдонимы могут быть использованы для переименования таблиц и колонок. В отличие от настоящих имен, псевдонимы могут не соответствовать ограничениям базы данных и могут содержать до 255 знаков (включая пробелы, цифры и специальные символы). Эта возможность SQL, которая поддерживается почти всеми реляционными системами управления базами данных.
How to Use Aliases in 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:
Let’s apply this to a practical example. We’ll use the following table, which contains basic information about our customers :
| customer_id | customer_name | age |
|---|---|---|
| 1 | Daniel Case | 23 |
| 2 | Josh Beverly | 47 |
| 3 | Bruce Roman | 51 |
| 4 | William Stewart | 62 |
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:
Executing this query will return this result set:
| customer_id | customer_name | age |
|---|---|---|
| 1 | Daniel Case | 23 |
| 2 | Josh Beverly | 47 |
| 3 | Bruce Roman | 51 |
Now let’s introduce our column aliases. The query below now contains an alias for two columns:
| id | name | age |
|---|---|---|
| 1 | Daniel Case | 23 |
| 2 | Josh Beverly | 47 |
| 3 | Bruce Roman | 51 |
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:
If the alias you use contains spaces, you must put quotes around the alias. For example:
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:
Let’s apply this syntax to an example. We’ll use the same table:
customers
Executing this query results in the following data being returned:
| customer_name | age |
|---|---|
| Daniel Case | 23 |
| Josh Beverly | 47 |
| Bruce Roman | 51 |
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.
| id | employee_name | department_id | manager_id |
|---|---|---|---|
| 1 | John Walker | 4 | NULL |
| 2 | Bob Pendergast | 1 | 1 |
| 3 | Penelope Cruz | 2 | 1 |
| 4 | Lucy Summers | 5 | 1 |
| 5 | Tim Saunter | 3 | 1 |
| 6 | Mary Smith | 2 | 3 |
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:
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:
| Employee | Manager |
|---|---|
| John Walker | NULL |
| Bob Pendergast | John Walker |
| Penelope Cruz | John Walker |
| Lucy Summers | John Walker |
| Tim Saunter | John Walker |
| Mary Smith | Penelope Cruz |
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 :
| Employee | Manager |
|---|---|
| Bob Pendergast | John Walker |
| Penelope Cruz | John Walker |
| Lucy Summers | John Walker |
| Tim Saunter | John Walker |
| Mary Smith | Penelope Cruz |
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.
SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
Alias Column Syntax
Alias Table Syntax
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
And a selection from the "Orders" table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10354 | 58 | 8 | 1996-11-14 | 3 |
| 10355 | 4 | 6 | 1996-11-15 | 1 |
| 10356 | 86 | 6 | 1996-11-18 | 2 |
Alias for Columns Examples
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
Example
The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: It requires double quotation marks or square brackets if the alias name contains spaces:
Example
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
Example
Note: To get the SQL statement above to work in MySQL use the following:
Alias for Tables Example
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):