SQL Aliases | Temporary Name to Column or Table

Aliases in SQL are temporary names you can give to a table or column for a particular SQL Query. They are useful when original names are very big or not readable. However, the temporary name only exists for the duration of the query. In this article, we will discuss SQL Alias. Also, we will discuss a few examples of using it.

Syntax

You can specify aliases in SQL using the AS keyword.

Column Aliases

SELECT column1 AS col1, column2 AS col2 FROM table_name;

  • column1, column2: The original columns names in the table.
  • col1, col2: The temporary names for column1 and column2 respectively for the duration of the query.
  • table_name: The original name of the table.

Table Aliases

Similarly, you can also provide an alias to a table name in SQL.

SELECT column1, column2 FROM table_name AS t1;

  • column1, column2: The original columns names in the table.
  • table_name: The original name of the table.
  • t1: The temporary name of the table for the duration of the query.

Demo Tables

Consider the following tables to be referenced for all the queries in this article.

mysql> select * from employees;
+----+--------+--------+------------+-------+---------------------+
| id | name   | salary | experience | team  | joiningDate         |
+----+--------+--------+------------+-------+---------------------+
|  1 | David  |  20000 |          5 | Alpha | 2019-01-01 00:00:00 |
|  2 | Monica |  26000 |          7 | Alpha | 2019-03-01 00:00:00 |
|  3 | John   |  15000 |          3 | Beta  | 2019-06-24 00:00:00 |
|  4 | Mary   |  18000 |          4 | Gamma | 2019-04-10 00:00:00 |
|  5 | Ross   |  30000 |          9 | Beta  | 2019-05-02 00:00:00 |
+----+--------+--------+------------+-------+---------------------+

mysql> select * from contact_details;
+----+---------------------+---------+
| id | emailId             | phone   |
+----+---------------------+---------+
|  1 | [email protected]  |    1234 |
|  2 | [email protected] | 4342342 |
|  3 | [email protected]   |   32342 |
|  4 | [email protected]   |    3234 |
|  5 | [email protected]   | 2342342 |
+----+---------------------+---------+

Examples

Let’s consider a few examples for demonstrating the use of aliases in SQL.

Example 1: Alias For Column Name

For instance, we can select the rows from the employees table and use temporary names for their columns.

mysql> SELECT name AS employeeName, team AS teamName FROM employees;
+--------------+----------+
| employeeName | teamName |
+--------------+----------+
| David        | Alpha    |
| Monica       | Alpha    |
| John         | Beta     |
| Mary         | Gamma    |
| Ross         | Beta     |
+--------------+----------+

You can observe in the example that the temporary column names are used and not the original columns.

Example 2: Alias For SQL Functions

Also, you can also assign aliases for SQL functions like the count function or the sum function.

mysql> SELECT sum(experience) AS sumOfExperience FROM employees;
+-----------------+
| sumOfExperience |
+-----------------+
|              28 |
+-----------------+

Example 3: Alias For Concat Function

Similarly, you can also assign an alias for the Concat Function in SQL.

mysql> SELECT CONCAT(name, '-', team) AS employeeTeam FROM employees;
+--------------+
| employeeTeam |
+--------------+
| David-Alpha  |
| Monica-Alpha |
| John-Beta    |
| Mary-Gamma   |
| Ross-Beta    |
+--------------+

Example 4: Aliases For Table Names

You can also provide aliases for table names in SQL. They are useful when there are more than one tables in a single query. In the example below, we fetch data from both employees and contact_details table using an INNER Join.

mysql> SELECT emp.name, cd.emailId, cd.phone FROM employees AS emp INNER JOIN contact_details AS cd ON emp.id = cd.id; 
+--------+---------------------+---------+
| name   | emailId             | phone   |
+--------+---------------------+---------+
| David  | [email protected]  |    1234 |
| Monica | [email protected] | 4342342 |
| John   | [email protected]   |   32342 |
| Mary   | [email protected]   |    3234 |
| Ross   | [email protected]   | 2342342 |
+--------+---------------------+---------+

You can observe that you can reference the columns from an individual table using the table alias. Therefore, aliases in SQL help to reference to a specific column from the table if both the table contains a column with the same name.

Similarly, you can use the table alias names in the WHERE condition.

mysql> SELECT emp.name, cd.emailId, cd.phone FROM employees AS emp INNER JOIN contact_details AS cd ON emp.id = cd.id WHERE emp.experience > 5;
+--------+---------------------+---------+
| name   | emailId             | phone   |
+--------+---------------------+---------+
| Monica | [email protected] | 4342342 |
| Ross   | [email protected]   | 2342342 |
+--------+---------------------+---------+

Conclusion

In conclusion, we discussed the SQL Aliases in this article. You can read more about it on Wikipedia. Additionally, you can also learn more about SQL on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *