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 | +---------+------------------+ |
|
Table: employees |
Table: departments |
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.
Example
Try this code »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:
Example
Try this code »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:
Example
Try this code »-- 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:
Example
Try this code »-- 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 | +--------------+------------------+
