SQL Alias

Summary: in this tutorial, you will learn about SQL alias including table and column aliases to make your queries shorter and more understandable.

Introduction to SQL alias

SQL alias allows you to assign a table or a column a temporary name during the execution of a query. There are two types of aliases: table alias and column alias.

Almost all relational database management system supports both column and table aliases.

Column alias

When we design the tables, we often keep the column names short e.g., so_no for the sales order number and inv_no for the invoice number. Then we use the SELECT statement to query the data from the table, the output is not descriptive.

To assign a column a new name in a query, you use the column alias. The column alias is just a temporary name of the column during the execution of a query.

See the following query:

SELECT
	inv_no AS invoice_no,
	amount,
	due_date AS 'Due date',
	cust_no 'Customer No'
FROM
	invoices;

In this example, we have several column aliases:

• The invoice_no is the alias of the inv_no column
• The 'Due date' is the column alias of the due_date column. Because the alias contains space, we have to use either sing quote (‘) or double quotes (“) to surround the alias.
• The 'Customer no' is the alias of the cust_no column. You notice that we did not use the AS keyword. The AS keyword is optional so you can omit it.

We often use the column aliases for the expressions in the select list. For example, the following query uses headcount as the column alias of the expression that returns the number of employees:

SELECT
	count(employee_id) headcount
FROM
	employees;

You can use the column alias in any clause evaluated after the SELECT a clause such as the HAVING clause. See the following example:

SELECT
	department_id,
	count(employee_id) headcount
FROM
	employees
GROUP BY
	department_id
HAVING
	headcount >= 5;

In the HAVING clause, instead of referring to the expression count(employee_id), we refer to the column alias headcount.

Table alias

We often assign a table a different name temporarily in a SELECT statement. We call the new name of the table is the table alias. A table alias is also known as a correlation name.

Notice that assigning an alias does not actually rename the table. It just gives the table another name during the execution of a query.

In practice, we keep the table alias short and easy-to-understand. For example, e for employees, d for departments, j for jobs, and l for locations.

So why do we have to use the table alias?

The first reason to use the table alias is to save time typing a lengthy name and make your query more understandable. See the following query:

SELECT 
    d.department_name
FROM
    departments AS d

d is the table alias of the  departments table. The AS keyword is optional so you can omit it.

When the  departments the table has the alias d, you can use the alias to refer to the table.

For example, the d.department_name refers to the department’s column of the table. If you don’t use the table alias, you have to use the departments.department_name to refer to the department_name, which is longer.

The second reason to use the table alias is when you want to refer to the same table multiple times in a single query. You often find this kind of query in the inner join, left join, and self-join.

The following query selects data from employees and departments tables using the inner join clause.

SELECT
	employee_id,
	first_name,
	last_name,
	department_name
FROM
	employees
INNER JOIN departments ON department_id = department_id
ORDER BY
	first_name;

The database system will issue an error:

 Column 'department_id' in on clause is ambiguous

To avoid the error, you need to qualify the column using the table name as follows:

SELECT
	employee_id,
	first_name,
	last_name,
	employees.department_id,
	department_name
FROM
	employees
INNER JOIN departments ON departments.department_id = employees.department_id
ORDER BY
	first_name;

To make the query shorter, you use the table aliases, for example, e for employees table and d for departments table as the following query:

SELECT
	employee_id,
	first_name,
	last_name,
	e.department_id,
	department_name
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
ORDER BY
	first_name;

The following query uses the self-join to join the employees to itself.

SELECT
	e.first_name AS employee,
	m.first_name AS manager
FROM
	employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY
	manager;

Because the  employees the table appears twice in the query, we had to use the table aliases e and m; code e stands for the employee andm stands for the manager.

In this tutorial, you have learned how to use the SQL alias including column alias and table alias to make your query shorter and more understandable.