SQL Standard

SQL Table Alias

SQL is an extremely powerful tool for interacting and managing the relational databases. One advantage of SQL queries is that they follow a specific syntax and are easy to port regardless of the database engine.

SQL statements can vary from simple statements to massive and complex multi-purpose queries. When dealing with multiple and complex SQL queries, it can become difficult to keep track on which table, especially those that share similar names but from different schemas, etc.

This is where the table aliases come into play. Table aliases in SQL are an exceptional tool that allow us to set the alternative names for a given table, making it easier to identify in a query.
In this tutorial, we will walk you through everything that you need to know when dealing with SQL table aliases.

SQL Table Alias

In SQL, a table alias refers to a temporary name that is assigned to a given table or table column during the lifetime of the SQL query.

The main role of a table alias is to allow us to reference the tables using a shorter and more memorable name which can mean a lot in complex queries that involves multiple tables.

In SQL, we can define a table alias using the AS keyword as shown in the following example syntax:

SELECT column1, column2, ...
FROM table_name AS alias name
WHERE condition;

In this case, we use the AS keyword followed by an alternative name that we wish to give to the target table.

Uses of SQL Table Alias

There are several use cases of table aliases in SQL, some of them include the following:

  • Readability – Aliases make SQL queries more human-readable by providing shorter and more meaningful names for tables and columns.
  • Self-Joins – When performing a join on a table against itself, you need a self-join to differentiate between them.
  • Subqueries – We also use the table aliases when working with subqueries to distinguish between tables in the main query and the ones in the nested query.

Examples:

Let us explore the various examples and usages of the SQL table column as shown in the next sections of this tutorial.

Example 1: Basic Usage

Suppose we have a database containing two tables. One is the “employees” table and the other is the “department” table. We want to retrieve a list of employees in correspondence with their department. We can use the table alias as shown in the following example:

SELECT e.employee_name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;

In this case, we are using the table aliases to assign the “e” and “d” aliases to the “employees” and “departments” tables, respectively.

This can in-turn make the query easier to read and more approachable even if it is buried in a complex SQL statement.

Example 2: Working with Self-Join

As we mentioned, table aliases are useful when you need to perform a self-join. Let us take the sample Sakila database. Suppose we want to find the actors who appeared in the same film.

We can use a table alias as demonstrated in the following example query:

SELECT a1.actor_id AS actor1_id, a1.first_name AS actor1_first_name, a1.last_name AS actor1_last_name,
       a2.actor_id AS actor2_id, a2.first_name AS actor2_first_name, a2.last_name AS actor2_last_name
FROM actor AS a1
JOIN actor AS a2 ON a1.actor_id  a2.actor_id;

In this example, we use the “a1” and “a2” table aliases for the “actor” table to distinguish between two instances of the same table.

NOTE: To ensure that we do not match an actor with themselves, we introduce the ON clause and a conditional to check for the same actor ID.

This should perform a self-join on the table and return the first 10 matching rows as shown in the following example output:

Example 3: Using the Table Aliases with SQL Subquery

Lastly, let us look at an example of using the table aliases within an SQL subquery.

Suppose we wish to find the actors who appeared in the same film as a specific actor. We can use a subquery in conjunction with table aliases to accomplish this as follows:

SELECT DISTINCT a.actor_id, a.first_name, a.last_name
FROM actor AS a
JOIN film_actor AS fa1 ON a.actor_id = fa1.actor_id
JOIN film_actor AS fa2 ON fa1.film_id = fa2.film_id
WHERE a.actor_id <> 1 LIMIT 10;

This should return all the actors who appeared in the same film as the specified actor. You will notice an extensive use of table aliases for maximum and efficient query usage.

Conclusion

In this tutorial, we learned everything there is to know about table aliases in SQL. You can also check out our tutorial on column aliases in MySQL for a more detailed information.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list