(SQL Tutorials for Citizen Data Scientist)

SQL Aliases

In this tutorial you will learn how to specify a short alias name for a table or a table column within an SQL statement.

Defining Table Aliases

When multiple tables are being joined in a single query, you need to prefix each column name with the name of the table it belongs to, like employees.dept_id, departments.dept_id, etc. in order to avoid the confusion and ambiguous column error in case columns in different tables have the same name. But, if table names are long and appears several times in the query then writing the query would become a difficult and annoying task.

So to save time and avoid writing the complete table names, you can give each table a short alias name and refer to its columns using that alias name in the query.

To understand this clearly, let’s look at the following employees and departments tables.

+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+

Here’s a query that retrieves the employee’s id, name and their department name by joining the employees and departments tables together using the common dept_id field.

SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees LEFT JOIN departments
ON employees.dept_id = departments.dept_id ORDER BY emp_id;

Here’s the compact version of the previous query that uses table aliases:

SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;

If you execute any of these statements, you’ll get the same output, as follow:

+--------+-----------------+--------------------+
| emp_id | emp_name        | dept_name          |
+--------+-----------------+--------------------+
|      1 | Ethan Hunt      | Human Resources    |
|      2 | Tony Montana    | Administration     |
|      3 | Sarah Connor    | Sales              |
|      4 | Rick Deckard    | Finance            |
|      5 | Martin Blank    | NULL               |
+--------+-----------------+--------------------+

As you can see how much typing effort we can save by using the table aliases.

Check out the SQL JOINS section to learn more about table joins.

Defining Aliases for Table Columns

In MySQL, when you use the SQL function to generate a customized output the name of the output column might not be human readable or very difficult to understand. In that case, you can use the aliases to temporarily give a different name to the output column.

Consider the following query in which we’ve used an expression to reformat the dates in the hire_date column for generating a custom output:

-- Syntax for MySQL Database 
SELECT emp_name, DATE_FORMAT(hire_date, '%M %e, %Y') FROM employees;

If you execute the above statement, you’ll get the output something like this:

+--------------+-------------------------------------+
| emp_name     | DATE_FORMAT(hire_date, '%M %e, %Y') |
+--------------+-------------------------------------+
| Ethan Hunt   | May 1, 2001                         |
| Tony Montana | July 15, 2002                       |
| Sarah Connor | October 18, 2005                    |
| Rick Deckard | January 3, 2007                     |
| Martin Blank | June 24, 2008                       |
+--------------+-------------------------------------+

As you see the label of the last column in our output is long and unwieldy. We can fix this problem using the column aliases, as follow:

-- Syntax for MySQL Database 
SELECT emp_name, DATE_FORMAT(hire_date, '%M %e, %Y') AS hire_date
FROM employees;

If you execute the above statement, you’ll get more readable output, as follow:

+--------------+------------------+
| emp_name     | hire_date        |
+--------------+------------------+
| Ethan Hunt   | May 1, 2001      |
| Tony Montana | July 15, 2002    |
| Sarah Connor | October 18, 2005 |
| Rick Deckard | January 3, 2007  |
| Martin Blank | June 24, 2008    |
+--------------+------------------+

Beginners Guide to SQL – SQL Aliases

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.