SQL Aliases

SQL aliases are used to create a temporary name for columns or tables.

An alias exists temporarily during the execution of a query.

The SQL aliases can be helpful in the following cases:

• When there is more than one table used in a query.
• When column names are long or not readable.
• When functions are used in a query.
• When two or more columns are combined together.

Syntax

The syntax to alias a column is as follows:

SELECT column_name AS alias_name
FROM table;

The syntax to alias a table is as follows:

SELECT column_list 
FROM table_name AS alias_name;

Parameters:

• column_name: The original name of the column that you want to alias.
• table_name: The original name of the table that you want to alias.
• alias_name: The temporary name that you want to assign as an alias.

The alias name has some particularity that we can detail in the following points:

• When the alias_name contains space, you must enclose the alias_names in double quotes or square brackets.
• It is acceptable to use spaces when aliasing a column name. On the other hand, it is a bad practice to use spaces when aliasing a table name.
• Tha alias_name is only valid within the scope of the SQL statement.

Demo Table

To understand the aliases in SQL, let us consider the following "Employee" and "Department" tables:

• Table Employee:

  +--------+-------------+-----------+-----------+---------+
  | emp_no | first_name  | last_name | salary    | dept_id |
  +--------+-------------+-----------+-----------+---------+
  | 1001   | James       | Smith     | 6600      | 1       |
  | 1002   | Maria       | Martinez  | 9000      | 2       |
  | 1003   | Andrew      | Rodriguez | 9500      | 3       |
  | 1004   | Robert      | Hernandez | 9600      | 2       |
  | 1005   | Donna       | Williams  | 7400      | 3       |
  | 1006   | James       | Johnson   | 8000      | 5       |
  +--------+-------------+-----------+-----------+---------+
  

• Table Department:

  +---------+------------------+
  | dept_id | dept_name        |
  +---------+------------------+
  | 1       | Customer Service |
  | 2       | Development      |
  | 3       | Finance          |
  | 4       | Human Resources  |
  | 5       | Marketing        |
  | 6       | Production       |
  | 7       | Research         |
  | 8       | Sales            |
  +---------+------------------+
  

Alias for Columns Examples

Often, aliases are utilized to make the column headings readable in the result set. Columns Aliases are also utilized when an aggregate function is used like MIN, MAX, COUNT, AVG, SUM.

The following SQL statement creates three aliases, one for the "emp_no" column, one for the "first_name" column, and one for the "last_name" column.

SELECT emp_no AS Number, first_name AS FirstName, last_name AS LastName
FROM employee;

The output will be as follows:

+--------+-----------+-----------+
| Number | FirstName | LastName  |
+--------+-----------+-----------+
| 1001   | James     | Smith     |
| 1002   | Maria     | Martinez  |
| 1003   | Andrew    | Rodriguez |
| 1004   | Robert    | Hernandez |
| 1005   | Donna     | Williams  |
| 1006   | James     | Johnson   |
+--------+-----------+-----------+

The following SQL statement creates two aliases, one for the "first_name" column, one for the "last_name" column.

SELECT first_name AS "First Name", last_name AS [Last Name], salary
FROM employee;

After the execution, the result set will be as follows:

+------------+-----------+--------+
| First Name | Last Name | salary |
+------------+-----------+--------+
| James      | Smith     | 6600   |
| Maria      | Martinez  | 9000   |
| Andrew     | Rodriguez | 9500   |
| Robert     | Hernandez | 9600   |
| Donna      | Williams  | 7400   |
| James      | Johnson   | 8000   |
+------------+-----------+--------+

Note: If you want to use spaces within an alias, you need to use double quotation marks or square brackets.

The following SQL statement creates an alias called "Name", which combines two columns ("first_name" and "last_name").

SELECT first_name + " " + last_name AS name, salary 
FROM employee;

After running the above SQL statement, the output will be as follows:

+------------------+--------+
| name             | salary |
+------------------+--------+
| James Smith      | 6600   |
| Maria Martinez   | 9000   |
| Andrew Rodriguez | 9500   |
| Robert Hernandez | 9600   |
| Donna Williams   | 7400   |
| James Johnson    | 8000   |
+------------------+--------+

If you want the above SQL statement to work in MySQL, you need to use the CONCAT function as follows:

• For MySQL:

  SELECT CONCAT(first_name , " ", last_name) AS name, salary
  FROM employee;
  

Alias for Tables Examples

Generally, you use an alias for a table when you want to utilize the same table name more than once (Self Join), or when you want to shorten the table name to make it more readable.

The following SQL statement fetches the employee's first and last names and their department name by joining the "Employee" and "Department" tables using the common "dept_id" table.

SELECT em.first_name, em.last_name, de.dept_name
FROM employee AS em 
INNER JOIN department AS de
ON em.dept_id = de.dept_id;

The following SQL statement is the same as above without aliases.

SELECT employee.first_name, employee.last_name, department.dept_name
FROM employee 
INNER JOIN department
ON employee.dept_id = department.dept_id;

After executing one of the above SQL statement, the result set will be as follows:

+------------+-----------+------------------+
| first_name | last_name | dept_name        |
+------------+-----------+------------------+
| James      | Smith     | Customer Service |
| Maria      | Martinez  | Development      |
| Andrew     | Rodriguez | Finance          |
| Robert     | Hernandez | Development      |
| Donna      | Williams  | Finance          |
| James      | Johnson   | Marketing        |
+------------+-----------+------------------+