SQL aliases are like human aliases. They assign a temporary name to a table or a column in a table. Aliases are useful because they help make queries and their results more readable and easier to interpret.
In this guide, we talk about what SQL aliases are and why they are used. We walk through two scenarios of an alias in action to help you understand how to use SQL aliases. Without further ado, let’s begin!
Note: This tutorial has been written using PostgreSQL.
SQL Aliases: Columns
You use aliases on column names. This lets you change the name of a column in the output of a query. Column aliases are used with aggregate functions.
Let’s take a look at two tables in a database: employees and branches.
|Geoff||Senior Sales Associate||1||38000||1||San Francisco|
|Adam||Senior Sales Associate||2||38000||2||Cambridge|
|Emma||Marketing Executive||2||50000||3||Los Angeles|
The employees table has four columns and four rows. The branches table has two columns and three rows.
Let’s write a SELECT query that tells us the highest salary earned at each branch:
SELECT branches.branch_name, MAX(employees.salary) FROM employees LEFT JOIN branches ON employees.branch_id = branches.branch_id GROUP BY branches.branch_name;
We use the MAX() aggregate function to select the highest value in the “salary” column of the “employees” table. We use a LEFT JOIN statement to connect the “branches” and “employees” tables together. This lets us see the return of each branch. Let’s see what our query returns:
Our query works. The title names of our query are not very intuitive. For someone who does not write this, query “max” may be confusing.
We solve this by specifying column alias names:
SELECT branches.branch_name AS "Branch Name", MAX(employees.salary) as "Highest Salary" FROM employees LEFT JOIN branches ON employees.branch_id = branches.branch_id GROUP BY branches.branch_name;
We specify two aliases. “branch_name” gets the alias “Branch Name”. Our MAX() aggregate function gets the alias “Highest Salary”. Let’s run our query again:
|Branch Name||Highest Salary|
The returning rows by our query are the same. The table headings change. Now, “branch_name” is “Branch Name” and “max” is “Highest Salary”.
SQL Aliases: Tables
You can define an SQL alias on a table.
When you query multiple tables, you need to specify the name of each table before the name of the columns that you are working with. This ensures SQL knows exactly which table each statement refers to in your query.
While this feature protects you from making your queries ambiguous, it makes them less readable.
Let’s write a query that returns the name of every employee alongside the name of the branch at which they work:
SELECT employees.name, branches.branch_name FROM employees LEFT JOIN branches ON employees.branch_id = branches.branch_id;
Our query returns:
We query two tables: employees and branches. We use a LEFT JOIN to connect the value of “branch_id” in both tables. This allows us to see the names of each branch with which an employee is associated.
Our query is quite long. We repeat the words “employees” and “branches” twice. In a longer query, we have to use these words even more.
We use aliases to make our query shorter. We need to use the AS keyword to create a table name as an alias in an SQL query:
SELECT e.name, b.branch_name FROM employees AS e LEFT JOIN branches AS b ON e.branch_id = b.branch_id;
This query returns the same output as our first query.
We assign two aliases:
- “employees” is “e”
- “branches” is “b”
Any time we refer to “e” or “b” in our query, SQL treats it as if we are referring to “employees” and “branches”, respectively. Our table aliases allow us to reduce the amount of repetition in our SQL statement.
SQL aliases make it easy to write more readable queries. You use aliases to shorten the names of tables in queries or change the column headers in the result of a query.
Now you’re ready to use aliases in SQL like a professional database administrator!