SQL Alias - Temporary Names in RDBMS

<< Previous - SQL NULL Value

Next - SQL SELECT TOP >>

What are SQL Aliases?

• SQL Aliases are used to assign the temporary name to a table or a column in a table.
• The alias name can exist only for the duration of that query.
• The actual name of a table or column does not change.
• The AS keyword is used to create the alias.
• The alias can improve the table's readability.

SQL - When to use Alias

• To give a more relevant name to a table or column.
• To use more than one table in a single query.
• To combine two or more column.
• The table or column name is big and not easy to read.
• Need to use Functions in the query.

SQL Alias - Syntax

• SQL Column Alias

  
  SELECT Column_name AS Alias_Name
  FROM Tabe_name
  WHERE condition;
  
  

• SQL Table Alias

  
  SELECT Column_name1,Column_name2....
  FROM Table_name AS Alias_Name
  WHERE Condition;
  
  

SQL Alias - Example

Consider the following two tables:

1. Student table

   
   ++--------+---------+------+-----------+
   | RollNo | Name    | Age  | City      |
   +--------+---------+------+-----------+
   |      1 | Aruna   |   18 | Chennai   |
   |      2 | Varun   |   19 | Bangalore |
   |      3 | Ara     |   19 | Kerala    |
   |      4 | Markdin |   18 | Mumbai    |
   |      5 | Kannan  |   20 | Kerala    |
   |      6 | Aruna   |   18 | Chennai   |
   |      7 | Jose    |   19 | Kerala    |
   +--------+---------+------+-----------+
   
   

2. Student_Mark Table

   +--------+---------+-----------+------+
   | RollNo | Name    | City      | Mark |
   +--------+---------+-----------+------+
   |    101 | Sakthi  | Chennai   |   80 |
   |    102 | Bala    | Chennai   |   86 |
   |    103 | Chandra | Bangalore |   89 |
   |    104 | Madhan  | Goa       |   80 |
   |    105 | Jose    | Kerala    |   82 |
   |    106 | Jithu   | Kerala    |   85 |
   |    107 | Veni    | Bangalore |   85 |
   |    108 | Aruna   | Delhi     |   75 |
   +--------+---------+-----------+------+
   

The following statement use the column alias and will produce the result

mysql> SELECT Rollno AS ID_NO, Name AS Student_Name,City
       FROM student;
+-------+--------------+-----------+
| ID_NO | Student_Name | City      |
+-------+--------------+-----------+
|     1 | Aruna        | Chennai   |
|     2 | Varun        | Bangalore |
|     3 | Ara          | Kerala    |
|     4 | Markdin      | Mumbai    |
|     5 | Kannan       | Kerala    |
|     6 | Aruna        | Chennai   |
|     7 | Jose         | Kerala    |
+-------+--------------+-----------+
7 rows in set (0.00 sec)

The following statement use the Table alias and will produce the result

mysql> SELECT S.Rollno,S.Name,M.Mark FROM student AS S, Student_Mark AS M
	WHERE M.name = S.name;
+--------+-------+------+
| Rollno | Name  | Mark |
+--------+-------+------+
|      1 | Aruna |   75 |
|      6 | Aruna |   75 |
|      7 | Jose  |   82 |
+--------+-------+------+
3 rows in set (0.00 sec)

<< Previous - SQL NULL Value

Next - SQL SELECT TOP >>