Pages

Monday 24 February 2014

Aliases

      Aliases are new name given to the columns in a table and the table itself when retrieving records from database using the select statements. They are only for display purpose and also help in preventing conflicts while using two or more tables in a single select statement. It is always a good practice to use alias when you are joining two or more tables.
 
The aliases can be of two types:


  • Alias defined for columns (Column Alias).
  •  Alias defined for tables (Table Alias).

Alias defined for columns:

    This is used to give a new name to the columns of a table while displaying it. Consider the below query that displays the employee names and sum of the salary and commission from employees table.


SELECT  first_name  Names,  salary+nvl(commission_pct,0)  AS  "TOTAL SALARY"
FROM employees

    In above query you can find the words in italics are the actual names of the columns of table employees and the words that are in red are alias to that columns. You can see in the below output image that actual names of columns are replaced by the aliases while displaying.




The ways in which column alias can be defined are as follows:

  •     Write the alias name immediately following the column in the select statement.
  •     Optional ‘AS keyword can be used between column name and alias name.
  •    If alias name contains spaces or special characters or it is case sensitive then alias name must be written in double quotation mark.

Alias defined for tables:

     This alias is not for display purpose. We go for table alias when we are using two or more tables in a single select statement.

      Consider, table employees and departments. The below images show the structures of the tables.



















     







 Try to run the query below.

SELECT   first_name, department_id, salary
FROM     employees, departments
WHERE   department_id=department_id;

     It will throw an error as column ambiguously defined. This is because both the table employees and departments have a column (department_id) which has same name. You can see in the above image, the columns are highlighted using circles.

      The above query can be rewritten using table alias as shown below.

SELECT     e.first_name, d.department_id, e.salary
FROM      employees e, departments d
WHERE    e.department_id = d.department_id

     In above query you can see letters highlighted in red color are table aliases. You can consider table aliases as pointer variables that hold the address of their respective tables.

The output of the above query is shown below.



Note: Column alias is used for display purpose only and table alias is for preventing conflicts between columns when using two or more tables in a single select statement.