When you join multiple tables in a single query, it's essential to prefix each column name with the table name it belongs to (e.g., employees.dept_id, departments.dept_id) to prevent confusion and ambiguous column errors, especially when tables share column names. However, if table names are lengthy and repeated frequently in the query, writing the query can become cumbersome and error-prone.
To streamline this process and save time, you can assign each table a short alias name. Then, you can refer to columns using these alias names in the query.
To understand this concept better, let's consider 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 fetches 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 a simplified version of the previous query that utilizes 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 will receive identical output, as shown below:
+--------+-----------------+--------------------+ | 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 | +--------+-----------------+--------------------+
Using table aliases saves a significant amount of typing effort, as demonstrated.
Explore the SQL JOINS section for more insights into table joins.
In MySQL, when using SQL functions to generate custom outputs, the column names may not be human-readable or easily understandable. To address this, you can use aliases to temporarily assign a different name to the output column.
Consider the following query where we use an expression to reformat dates in the hire_date column to create a customized output:
-- Syntax for MySQL Database
SELECT emp_name, DATE_FORMAT(hire_date, '%M %e, %Y') FROM employees;Executing the above statement will produce output similar to 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 can see, the label of the last column in our output is long and cumbersome. We can resolve this issue by using column aliases, as follows:
-- Syntax for MySQL Database
SELECT emp_name, DATE_FORMAT(hire_date, '%M %e, %Y') AS hire_date
FROM employees;Executing the above statement will result in a more readable output, as shown below:
+--------------+------------------+ | 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 | +--------------+------------------+