In this tutorial, you will learn about SQL aliases, including table and column aliases, to make queries shorter and easier to understand.

1. Introduction to SQL alias

SQL aliases are used to assign temporary names to tables or columns during query execution. There are two types of aliases: table aliases and column aliases.
Almost all relational database management systems support column aliases and table aliases.

1.1. Column alias

When we design a table, we often keep the column name short or give a new name. For example, the sales order number is: so_no, Invoice No.: inv_no. It is not descriptive when using the SELECT statement to query data from a table and return output.

To specify a new name for a column in a query, use a column alias. The column name is only the temporary name of the column during query execution. For more tutorials, visit http://www.manongzj.com

See the following query:

SELECT
    inv_no AS invoice_no,
    amount,
    due_date AS 'closing date',
    cust_no 'Customer number'
FROM
    invoices;

• invoice_no is inv_ Alias of no column
• 'Due date 'is due_ Column alias for the date column. Because the alias contains spaces, you must enclose the alias with single quotation marks (') or double quotation marks (").
• 'Customer no' is cust_ Alias of the no column. Note that the AS keyword is not used here. The AS keyword is optional, so it can be omitted.

We often use column aliases for expressions in the selection 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;

Execute the above example code and get the following results-

mysql> SELECT
    count(employee_id) headcount
FROM
    employees;
+-----------+
| headcount |
+-----------+
|        40 |
+-----------+
1 row in set

Column aliases can be used in any clause evaluated after the SELECT clause, such as HAVING clause . See the following example:

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

Execute the above example code and get the following results-

 

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

1.2. Table alias

Temporarily assign a different name to the table in the SELECT statement. The new name of this table is called the table alias. Table aliases are also known as related names.

Note that assigning aliases does not actually rename the table. It simply provides another name for the table when the query is executed.

In practice, we keep table aliases short and easy to understand. For example, e represents employees, d represents departments, j represents positions, and l represents positions.

So why do you have to use table aliases?

The first reason: using table aliases is to save time entering verbose names and make queries easier to understand. 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 it can be omitted.

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

For example, d.department_name refers to the Department of the departments table_ Name field. If you do not use a table alias, you must use departments department_name to reference a longer department_name field.

The second reason: using table aliases, you want to reference the same table multiple times in a single query. For example, often in Inner connection，Left join and Self connection Such queries were found in.

The following query uses the inner join clause to select data from the employees and departments tables.

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

After executing the above query statement, the database system will issue an error:

Column 'department_id' in on clause is ambiguous

To avoid this error, you need to qualify columns with table names, 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 can use the table alias. For example, e represents the employees table and d represents the departments table, as shown in 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 self join to self join the employee table.

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 table appears twice in the query, you need to use two table aliases: e and m; E represents the employee and M represents the manager.

In this tutorial, you learned how to use SQL aliases (including column aliases and table aliases) to make queries shorter and easier to understand.