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.
