MySQL Alias
We can use the MySQL Alias to give a shorter or more descriptive name to our tables and columns when using them in a Query. An Alias is a temporary name given to a column or a table and does not affect it in any way. We can use aliases for naming columns, naming tables to provide shorter names, and even subqueries.
MySQL Alias syntax
The Basic syntax of a column Alias is as follows:
We can skip the quotation marks if the Alias does not have spaces. However, if the Alias has spaces, then quotations are mandatory.
SELECT
<column_1 | expression> AS `descriptive_name`
FROM <table_name>;
For a Table Alias, the syntax is,
SELECT <columns>
FROM <table-name> <Alias>
Where <conditions>;
Column alias in MySQL
Here we are making a new column by concatenating two columns in the table and giving it an alias.
select concat(FIRST_NAME,' , ',LAST_NAME) AS Name
from employees;
To give a name with spaces, use quotes ('') as shown below,
select concat(FIRST_NAME,' , ',LAST_NAME) AS 'Full Name'
from employees;
Table alias in MySQL
A table alias is usually to give a table a shorter or temporary name. An Alias is handy when joining multiple tables and is useful in resolving the "ambiguous column" problem.
SELECT E.EMP_ID, E.FIRST_NAME,E.LAST_NAME,ED.DEPT_ID,ED.FROM_DATE,ED.TO_DATE
FROM EMPLOYEES E , EMPLOYEE_DEPT ED
WHERE E.EMP_ID = ED.EMP_ID
AND ED.TO_DATE IS NOT NULL;
Subquery alias in MySQL
We can use Subqueries to form complex queries, and most times, we need to access the records from the subquery for a condition. In such a scenario, we can give the Subquery an Alias and use it again and again.
SELECT E.FIRST_NAME,E.LAST_NAME,TEMP.SALARY,ED.DEPT_ID,D.DEPT_NAME
FROM
(SELECT MAX(SALARY) AS 'SALARY', DEPT_ID FROM EMPLOYEES E, EMPLOYEE_DEPT ED WHERE E.EMP_ID = ED.EMP_ID GROUP BY ED.DEPT_ID) AS TEMP
INNER JOIN EMPLOYEE_DEPT ED ON TEMP.DEPT_ID = ED.DEPT_ID
INNER JOIN EMPLOYEES E ON E.EMP_ID = ED.EMP_ID AND TEMP.SALARY = E.SALARY
INNER JOIN DEPARTMENTS D ON D.DEPT_ID = ED.DEPT_ID;
