MySQL Aliases

What are MySQL Aliases?

MySQL Aliases are the temporary names given to the MySQL tables and columns for ease of understanding. We can provide any user’s understandable name to the columns or table. This name is given by using AS keyword. Note that we will be using the same ‘students’ table we created earlier.

We will see how we can use MySQL Aliases on the table as well as on columns one by one.

1. Using Aliases on columns

Syntax:

select column_name AS alias_name from schema.table_name where condition;

Example: if we want to select the name column from the table but if we want to call that column as ‘student_name’ then we can give the alias name as shown below.

select name as student_name from students;

We can also select multiple columns and give alias names to each one as shown below. Make sure we are giving different aliases for each column.

Query:

select roll_num as student_no,
   name as student_name,
   marks as student_mark,
   class as student_class,
   gender as student_gender
from students; 

2. Using Aliases on MySQL table

Syntax:

select * from schema.table_name AS alias_name where condition;

As shown in the below example we have given an alias name for the ‘students’ table as ‘stu’ so we can use this name wherever required. 

Query:

select * from students as stu where stu.roll_num > 20;

This is helpful when we have to select from multiple tables.

For example, we have two tables, students and users and we need to select the data from both tables.

Assume we have 

the ‘Students’ table as

And the ‘users’ table as

If we want to select names for students as well as users whose roll_num and user_id are matching. We can write a query as follows:

SELECT
   stu.roll_num, usr.user_id, stu.name AS student_name, usr.user_name
FROM
   students AS stu,
   users AS usr
WHERE
   stu.roll_num = usr.user_id;

-A blog by Shwetali Khambe