MySQL Alias
Aliases in MySQL is used to give a temporary name to a table or a column in a table for the purpose of a particular query. It works as a nickname for expressing the tables or column names. It makes the query short and neat.
It is very useful when the table or column name is not user-friendly in real-time. It makes the name of a column more readable. MySQL aliases can exist only for the duration of a query.
Advantages of MySQL Aliases
Following are the advantage of an alias in MySQL:
- It is preferred in the case when more than one table is going to use in a query.
- It provides a very useful and flexible feature that allows us to achieve complex tasks quickly.
- It makes the column or table name more readable.
- It is useful when you use the function in the query.
- It can also allow us to combines two or more columns.
- It is also useful when the column names are big or not readable.
Syntax
Following are the basic syntax of aliases used in MySQL:
For Column
For Table
Parameter Explanations
The following table explains the arguments in detail:
| Parameter | Descriptions |
|---|---|
| Column_Name | It is the column name that we are going to create an alias name. |
| Table_Name | It is the table name that we are going to create an alias name. |
| Alias_Name | It is the temporary name that we are going to assign for the column or table. |
| AS | It is optional. If you have not specified it, there is no effect on the query execution. It is a programmer choice that they use it during the aliasing of the column name, but not aliasing in the table name. |
If you want to give the alias name with spaces, then it must be enclosed within quotes. It is generally a good practice to provide spaces in aliasing a column name, not a table name. The following syntax explains it more clearly:
Now, we are going to understand how aliasing works in MySQL. Let us first create a table named “Student_info” using the following query:
After creating the table, we need to insert a record. Suppose this table contains the following data:
Illustrating Column Aliases
If we want to fetch the stud_code from the above table using ‘Roll No’ as alias name, execute the following statement:
It will return the output as below:
Again if we want to fetch the stud_code using ‘Roll No’ that contains spaces and marks as scores, execute the following statement:
It will return the output as below:
It is another example that will return the student name and subject whose marks are greater than 70.
After the successful execution, we can see that the result set contains only those records who have marks greater than 70.
Illustrating Table Aliases
It allows us to specify the table name with a different name. Mostly, table aliases used for more than single tables and connect them using JOIN operations.
Suppose our database having a table named “Students” that contains the following data:
This statement will return the records using table aliases:
It will give the below output:
Let us see another example to understand the table aliases using JOIN operation. Suppose our database have one more table named “Student_detail” that contains the data as given below:
In both tables, we can see that they contain one column ‘stud_code’ same. If we use it without table aliases, it will give an error that says: column ‘stud_code’ in one clause is ambiguous.
So if we want to avoid this type of error, use the table aliases concept. This statement explains it more clearly:
After successful execution of the above statement, we will get the output as below:
If you do not want to use aliases concept with a query, then MySQL uses the table name for identifying the column name. This process makes the statement lengthy and less readable, which can be shown below: