MySQL Alias

Introduction to MySQL Alias

MySQL Alias is used when we are willing to provide an alternate name for the column or table. Temporarily assigning a name for column or table that is user friendly and understandable. Alias is mainly used for the column and table. Column Alias are used to give alternate name for the column headings which are easy for the end- user to understand. Table Alias is to provide an alternate table for the tables, which is used to make easier to read and use at the time of joins.

Syntax:

Given below is the syntax of alias how it is used in the column and table:

<column_name>AS<Alias_name>;

Here in the above syntax we provide the column name which we want alternate to be given. “AS” is optional. And at last we provide the alias name.

<table_name>AS<Alias_name>;

Here in the above syntax we provide the table name which we want alternate to be given. “AS” is optional. And at last we provide the alias name.

How does MySQL Alias works?

MySQL Alias works in the following way:

1. Column Alias

Now let us create a sample table as below and provide an alias for the columns:

Code:

CREATE TABLE EMPLOYEE_INFORMATION
(
EMPLOYEE_ID VARCHAR(10),
EMPLOYEE_NAME VARCHAR(20),
DEPT_ID VARCHAR(10),
EMPLOYEE_SAL INT
);

Now let us insert data into the above table:

Code:

INSERT INTO EMPLOYEE_INFORMATION VALUES ('E1','SAM','D1', 90000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E2','WILL','D1', 80000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E3','SOMY','D1', 50000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E4','FRED','D1', 10000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E5','HARRY','D2', 70000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E6','PONY','D2', 70000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E7','DAVID','D2', 40000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E8','PETER','D3', 30000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E9','RAM','D3', 60000);
INSERT INTO EMPLOYEE_INFORMATION VALUES ('E10','SAI','D3', 50000);

select * from EMPLOYEE_INFORMATION;

Output:

Now let us give an alias to each column above without an underscore in the column name.

Below example is for the column aliasing:

Code:

SELECT EMPLOYEE_ID AS "EMPLOYEE ID",
EMPLOYEE_NAME AS "EMPLOYEE NAME",
DEPT_ID AS "DEPARTMENT_ID",
EMPLOYEE_SAL AS "EMPLOYEE SALARY"
FROM EMPLOYEE_INFORMATION;

Output:

2. Table Alias

A table alias is mainly used when we are performing joins on the table.

Below are the sample table with sample data to show how the alias is performed.

Code:

CREATE TABLE SAMPLE_A
(
COL_A INT,
COL_B INT
);

Insert data into the table:

Code:

INSERT INTO SAMPLE_A VALUES (1, 5);
INSERT INTO SAMPLE_A VALUES (2, 4);
INSERT INTO SAMPLE_A VALUES (3, 3);
INSERT INTO SAMPLE_A VALUES (4, 2);
INSERT INTO SAMPLE_A VALUES (5, 1);

select * from SAMPLE_A;

Output:

Now let us create another table.

Code:

CREATE TABLE SAMPLE_B
(
COL_A INT,
COL_B INT
);

Insert data into the table:

Code:

INSERT INTO SAMPLE_B VALUES (1, 5);
INSERT INTO SAMPLE_B VALUES (2, 4);
INSERT INTO SAMPLE_B VALUES (3, 3);
INSERT INTO SAMPLE_B VALUES (4, 2);
INSERT INTO SAMPLE_B VALUES (5, 1);

Select * from SAMPLE_B;

Output:

Now let us perform joining on above and use alias:

a. Without Table alias

Code:

SELECT * FROM SAMPLE_A
JOIN SAMPLE_B
ON  SAMPLE_A.COL_A = SAMPLE_B.COL_A

Output:

b. With Table alias

Code:

SELECT * FROM SAMPLE_A A JOIN SAMPLE_B B
ON A.COL_A = B.COL_A

Output:

Examples of MySQL Alias

Given below are the examples:

Example #1 – Column alias

Now let us find the maximum and minimum salary that each department has for the above table which we created earlier.

Code:

SELECT DEPT_ID AS "DEPARTMENT ID",
MAX(EMPLOYEE_SAL)AS "MAXIMUM EMPLOYEE SALARY",
MIN(EMPLOYEE_SAL)AS "MINIMUN EMPLOYEE SALARY"
FROM EMPLOYEE_INFORMATION
GROUP BY DEPT_ID;

Output:

Example #2 – Table alias

If we consider a table giving an alternate name for the table is said to be table aliasing.

a. Without table alias

Code:

SELECT * FROM SAMPLE_A
JOIN SAMPLE_B
ON SAMPLE_A.COL_A = SAMPLE_B.COL_A

Output:

Here every time mentioning the whole table name in the joining condition would be difficult.

b. With Table alias

Code:

SELECT * FROM SAMPLE_A A JOIN SAMPLE_B B ON A.COL_A = B.COL_A

Output:

Here every time mentioning the whole table name in the joining condition would be difficult. Hence, we have mentioned the alias for the table. SAMPLE_A has alias name as “A”. And SAMPLE_B table has “B” alias.

We could see that ‘AS’ is not written while mentioning the alias.

We can mention alias as below as well:

Code:

SELECT * FROM SAMPLE_A AS A JOIN SAMPLE_B AS B ON A.COL_A = B.COL_A

Output:

Conclusion

Things that need to put in mind regarding the MySQL Alias are if you want space to be provided in the alias_name, then we must enclose the alias_name in quotes. Spaces are accepted when we are defining in the column name. However, it is not good practice to use space in the table name aliasing. The alias_name is only valid within the scope of the SQL statement.

Recommended Articles

This is a guide to MySQL Alias. Here we discuss the introduction to MySQL alias along with the working and respective examples. You may also have a look at the following articles to learn more –

1. MySQL IN Operator
2. MySQL Subquery
3. MySQL Constraints
4. MySQL MAX() Function