ALIAS/AS operator in MySql

    The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name does not change in the database. The column aliases are used to rename a table's columns for the purpose of a particular SQL query.

Let's create the table we will use Script: 
--first lets drop the table Test (if she exists, if not skip the drop line).
Drop table test;
create table test (id int,name varchar(20),email varchar(20),salary int ,de char(20));
--insert values into table
insert into test values (1,'Eve','[email protected]',1500,'HR');
insert into test values (2,'Jon','[email protected]',2500,'AD');
insert into test values (3,'Mike','[email protected]',3000,'AD');
insert into test values (4,'Paul','[email protected]',3200,'HR');
insert into test values (5,'Mary','[email protected]',1800,'IT');
insert into test values (6,'Jane','[email protected]',2200,'IT');
Check the table to see the data and the structure. 
SQLselect * from test ;
     ID NAME         EMAIL           SALARY  DE
 ---------- -------------------- -------------------- ---------- ---------
     1  Eve        [email protected]        1500   HR
     2  Jon        [email protected]        2500   AD
     3  Mike       [email protected]       3000   AD
     4  Paul       [email protected]       3200   HR
     5  Mary       [email protected]       1800   IT
     6  Jane       [email protected]       2200   IT
Very good Some examples using Aliases for columns: Example 1 :
  • ok, we have in our Test table the column "DE" which we know by now that is a abbreviation for Department.
  • now imagine we need to make a report with all the departments we have in our Test table. For us is going to be ok to understand the "DE" abbreviation but for some other persons this will be confusing.
  • so now the "AS" to work , as he will replace the column name with some other name we chose: 
SQLselect de as department from test;
Department

HR
AD
AD
HR
IT
IT
Note :- thou we have printed the column name "DE" as Department the actual column name was not changed, is just the result that will receive the Department column header. Example 2 :
  • we can use as well two strings as column header using the following syntax:
  • they have to be inside double quotes.
SQLselect de as "emp dept" from test;

emp dept
HR 
AD 
AD 
HR 
IT 
IT
Example 3:
  • we can use alias as well like this :
SQLselect de Department from test;

Department
HR 
AD 
AD 
HR 
IT 
IT
  • see that we didn't use the "AS" statement, it is possible to do so as well.

Examples using Aliases for tables:

  • in this case the alias will represent the entire table.

Aliases is more useful when

  • There are more than one tables involved in a query.
  • Functions are used in the query.
  • The column names are big or not readable.
  • More than one columns are combined together.
Example 1 : 
SQLselect a.id ,a.name from test a;
    ID NAME
 ---------- --------------------
     1 Eve
     2 Jon
     3 Mike
     4 Paul
     5 Mary
     6 Jane
  • we can see here the "a"(letter) and a "."(dot) followed by the column name.This way "a" will represent out Test table .
  • the association of the Test table with "a" is done at the end of our query .Note: we can give any character values to our identifier "a". it can be T , s, zx, etc We will use more of alias when we will get to join and union. For now try to understand the concepts of alias and practice using simple examples like above.

Copyright 2020. All Rights Reserved