intro
Follow this guide and become a master of the SQL alias mechanism offered by databases to give temporary names to columns and tables.
The SQL alias mechanism plays a crucial role in improving the readability and manageability of your queries. At the heart of this feature lies the AS keyword, which represents a powerful tool for assigning temporary names to tables, columns, and even subqueries.
In this guide, you will understand what an alias in SQL is, how it works, when and why to use it, and explore some best practices.
Let's dive in!
What Is an Alias in SQL?
An SQL alias is a temporary name assigned to a table or column within a query. Aliases are generally used to make the column and table names more readable or concise, or to resolve naming conflicts when combining multiple tables.
The SQL alias mechanism comes from the official ANSI specification, which means that most DBMS technologies implement it. Refer to the documentation of the specific database system for more information.
Why Use Aliases
You now know what an SQL alias is, but why should you assign a table or column a temporary name in the scope of a query? Well, there are at least three good reasons for doing that:
The only scenario where SQL aliases are actually required is when JOIN queries lead to naming conflicts. If you do not use an alias in your SQL query when joining two or more tables with the same name, you will get this error:
1
Not unique table/alias: 'table_name'
Similar to the above behavior, an error is raised when selecting a column that has the same name in more than one of the joined tables:
1
Column '<column_name>' in field list is ambiguous
Some database systems also require an SQL alias for subqueries used in the FROM clause.
Alias SQL Syntax and Rules
The syntax to define an alias in SQL is pretty simple:
1
[action] original_name [AS] alias_name
Where:
The syntax denoting how to define a SQL table alias is as follows:
1
SELECT alias_name.*
2
FROM table_name [AS] alias_name;
Similarly, you can use it to define a SQL column alias:
1
SELECT column_name AS alias_name
2
FROM table_name;
In most cases, aliases are rather short and simple. SQL table aliases sometimes consist of a single uppercase character, too. For example, players would become just P. In contrast, column aliases are more focused on clarity. Their goal is to describe the data contained in a column, especially when it does not have a meaningful name.
Once defined, alias column names cannot be used in all clauses of an SQL query. Instead, you can use the alias name to refer to a column only in GROUP BY, ORDER BY, or HAVING clauses. Trying to reference an alias in a WHERE clause will result in an error.
Consider the query below:
1
SELECT points AS P
2
FROM players
3
WHERE P > 50
This will produce the following error:
1
Unknown column 'P' in 'where clause'
When Is It Possible to Use an Alias in SQL
As mentioned above, you cannot use an alias SQL under all circumstances. Let's better understand what are the usage scenarios of aliases in SQL through real-world examples!
SQL Table Alias
Suppose you have the query below:
1
SELECT orders.date, orders.quantity, products.name
2
FROM orders
3
JOIN products ON orders.product_id = products.id;
Add another JOIN or select more columns, and the above query will become very hard to follow. The problem is that JOIN statements lead to a lot of boilerplate SQL code, making queries too verbose.
That is where the SQL table alias mechanism comes in!
Now, rewrite the same query with an alias for orders and one for products:
1
SELECT O.date, O.quantity, P.name
2
FROM orders O JOIN products P ON O.product_id = P.id;
The result set will be the same. The real benefits of this approach are the clarity and readability of the new query.
SQL Column Alias
Consider the following query:
1
SELECT department, AVG(salary)
2
FROM employees
3
GROUP BY department
4
HAVING AVG(salary) > 30000
This selects the names and average salary of the departments with an average salary greater than $30,000.
The query works like a charm, but it also involves a repetition of the AVG(salary) expression. You can avoid that by using an SQL column alias:
1
SELECT department, AVG(salary) AS avg_salary
2
FROM employees
3
GROUP BY department
4
HAVING avg_salary > 30000
The result will be the same, but this query is easier to maintain.
SQL Subquery Alias
Another scenario in which aliases improve the developer experience is subqueries. In MySQL, specifying an alias for subqueries is actually required.
Take a look at the MySQL query below:
1
SELECT name, points
2
FROM (
3
SELECT *
4
FROM players
5
WHERE points > 10000
6
);
This retrieves the best players with a subquery and then selects some information from them.
Try to run the above SQL code and you will get the following error:
1
Every derived table must have its own alias
To fix that, you must use an SQL subquery alias as follows:
1
SELECT best_players.name, best_players.points
2
FROM (
3
SELECT *
4
FROM players
5
WHERE points > 10000
6
) AS best_players;
Et voilà! The SQL error is now gone and you will get the desired result.
SQL Alias: Best Practices
Take a look at the list of some of the most important SQL alias best practices:
Conclusion
In this guide, you understood what an SQL alias is and how this mechanism works. You now know that specifying aliases for tables and columns makes queries easier to read and maintain. Thanks to the examples shown here, you have seen how to make the most of the alias SQL system.
Aliases are great for simplifying queries, but they can also generate confusion. Forget all that with DbVisualizer, a database client with advanced query writing capabilities! Thanks to its powerful autocomplete feature, you can get the columns associated with a table, even when referencing it via its alias. This is just one of the several features supported by the tool. Try DbVisualizer for free today!
FAQ
Is it possible to reference an SQL alias in the WHERE clause?
Standard SQL does not allow SQL column aliases to be referenced in a WHERE clause. This restriction is imposed because when the WHERE clause is executed, the column value may not yet have been evaluated.
How do aliases contribute to making self-joins in SQL possible?
Aliases in SQL make self-joins possible by allowing the same table to have distinct names within the same query. This prevents conflicts between columns and facilitates an easier understanding of the query's logic.
Can an alias in SQL have the same name as the original column or table?
Yes, an alias in SQL can have the same name as the original column or table. While that does not lead to errors, it does not make much logical sense. To avoid confusion and harness the real power of aliases, the best practice is to choose different names.
Does the alias SQL mechanism have an impact on the performance of a query?
No, the use of aliases in SQL does not have a significant impact on query performance. The use of a large number of aliases can introduce a very marginal performance overhead, but the impact is usually negligible.
Are aliases in SQL case-sensitive?
No, in most database systems, SQL aliases are not case-sensitive. At the same time, this depends on the specific database system being used. For instance, MySQL and PostgreSQL treat aliases as case-insensitive by default. Consult the official documentation of your database system to learn more.
