SQL Alias (Rename) – Columns, Tables, and More
When writing queries, functions, or procedures in SQL we often want to abbreviate and rename objects. In SQL this is known as an alias. In some cases we may want to use the ‘AS’ keyword. This isn’t a requirement of all databases although a good practice for readability.
We can alias several objects in SQL including:
- Columns
- Tables
- Views
- Subqueries
Alias Columns
When we alias a column, this can be done using the ‘AS’ keyword. Some databases do not require the keyword and can be left out. For readability, it’s best to keep this in.
Alias Columns in a Subquery
Column aliases can also be referenced through a subquery. When this is done we have to use the alias in our outer query otherwise the database does not recognize the request.
Right
When we reference our subquery we must make sure to reference our aliased columns. We cannot reference our original table columns since we have renamed them. If we do that we will get errors from our database related to no such columns existing.
Wrong
The above SQL query will result in an error since we provided the incorrect reference based on our renamed alias in our subquery. In this case SQLite will produce an error saying “[SQLITE_ERROR] SQL error or missing database (no such column: <column_name>)“.
Alias Tables and Views
We can alias tables just as we would with columns. As a result, once we alias a table the reference is then used throughout the rest of the query and joins. As an example, we can see the below query joining between Customer with an alias as ‘C’ and Invoice with the alias as ‘I’. Then when we select a specific column we can use the ‘C’ and ‘I’ table references instead of the database table names.
Views work the same way as table objects. We reference the view in our ‘FROM’ clause, add the ‘AS’ statement, and then create the new alias name.
Alias a Subquery
We can also alias a subquery as if it were a table or view. As a result, we can then use the subquery reference in our SELECT statement.
