How to Use Aliases in an SQL Query

SQL allows the programmer to use aliases for both table names and column names. By utilizing aliases, you can eliminate ambiguity and make your queries much more understandable.

To illustrate the use of aliases, let’s consider a query in which we must join a table to itself. In order to keep the query from failing due to ambiguity, as well as keep all our columns straight, we must utilize aliases.

The Table

Table: employees

The Problem

Design a query that produces a list of every employee and their boss. If an employee does not have a boss, he/she must still be included in the query results.

The solution to this problem is to join the employees table to itself, matching the Supervisor column to the EmpId column. To avoid ambiguity, we will use aliases. Since we need to include all the employees regardless of whether the join operation finds a match to their record, we will use a LEFT JOIN.

The Query

SELECT
    employees.EmpFirstName,
    employees.EmpLastName,
    bosses.EmpFirstName AS SupervisorFirst,
    bosses.EmpFirstName AS SupervisorLast
FROM
    employees
LEFT JOIN employees AS bosses
    ON employees.Supervisor = bosses.EmpId

Each time you see AS used, we are assigning an alias to a table or column. On one half of the join, we have all of the employees from the employees table. On the other half of the join, we have attached all of the employees who are supervisors. Therefore, we use the table alias “bosses” to join the table employees to itself. To distinguish employee names from supervisor names, we have used the column aliases “SupervisorFirst” and “SupervisorLast”.

The Output

Looking at this query output, it appears that Jim Cooper is the top dog in this company. Apparently, the only person to whom he answers is himself.