MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

SQL Alias: Everything You Need to Know About AS in SQL

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:

  • Increased readability: Aliases make queries easier to read, understand, and maintain by providing shorter or more descriptive names for tables and columns. This also applies to SQL subqueries.
  • Resolving naming conflicts: When multiple tables are involved in a query, aliases help resolve naming conflicts. A popular scenario where aliases are required is with SQL self-joins. When joining a table with itself, defining aliases is necessary to distinguish between the different instances of the same table.
  • More clarity in aggregation: When using SQL aggregate functions, such as COUNT(), SUM(), AVG(), the SQL alias feature allows you to assign meaningful names to the result columns. That makes the query output clearer.

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:

Copy
        
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:

Copy
        
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:

Copy
        
1 [action] original_name [AS] alias_name

Where:

  • action is an action performed before invoking an alias.
  • original_name is the name of the column or table you want to rename using the alias SQL mechanism;
  • alias_name is the new name you can now use to refer to the original column or table.

The syntax denoting how to define a SQL table alias is as follows:

Copy
        
1 SELECT alias_name.* 2 FROM table_name [AS] alias_name;

Similarly, you can use it to define a SQL column alias:

Copy
        
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:

Copy
        
1 SELECT points AS P 2 FROM players 3 WHERE P > 50

This will produce the following error:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
1 Every derived table must have its own alias

To fix that, you must use an SQL subquery alias as follows:

Copy
        
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:

  • Although optional, always specify AS when defining aliases to achieve greater readability.
  • Choose meaningful and descriptive column aliases.
  • Use concise table aliases to avoid unnecessary verbosity.
  • Maintain consistency in your alias SQL naming convention throughout your queries.
  • Always use table aliases when joining multiple tables to improve query readability and avoid ambiguity.
  • Consider aliases as a tool to document complex queries.
  • Use aliases only when they’re absolutely necessary to avoid introducing clutter and confusion into your queries.
  • Ensure that your aliases do not conflict with reserved keywords in SQL to prevent errors.

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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL GROUP BY Clause: What It Is, How It Works, How to Approach It

author Lukas Vileikis tags DbVisualizer MySQL SQL 6 min 2024-11-06
title

SQL Server Guide: Create a View Based on a Stored Procedure

author Antonello Zanini tags SQL SERVER 5 min 2024-11-05
title

Postgres DATEDIFF Equivalent Functions: Calculating Date and Time Differences

author Lukas Vileikis tags POSTGRESQL 6 min 2024-11-04
title

The Definitive Guide to the SQL Server GETDATE Function

author Antonello Zanini tags SQL SERVER 6 min 2024-10-31
title

CREATE SCHEMA PostgreSQL Statement: What, Why & When to Use

author Lukas Vileikis tags POSTGRESQL 4 min 2024-10-30
title

SQL EXPLAIN: The Definitive Tool to Optimize Queries

author Antonello Zanini tags MySQL POSTGRESQL SQL 12 min 2024-10-29
title

Everything You Need to Know About SQL Constraints: The What, Why, and How

author Lukas Vileikis tags BEGINNER MySQL SQL 8 min 2024-10-28
title

CONCAT_WS SQL Server Function: Ultimate Guide With Examples

author Antonello Zanini tags SQL SERVER 6 min 2024-10-24
title

The Ultimate Guide to the SQL Server Date Format

author Antonello Zanini tags DATE SQL SERVER 9 min 2024-10-23
title

Changing the root Password in MySQL: A Guide

author Lukas Vileikis tags MySQL SECURITY 6 min 2024-10-22

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗