In this article, you’ll learn how to use Oracle aliases, such as column and table aliases, to improve the readability of a query by making the heading of the result more understandable.
When you query data from a table, Oracle displays the column headings using the table’s column names. The following line, for example, returns the first and last names of employees:
SELECT first_name, last_name FROM employees ORDER BY first_name;
The names of the first name and last name columns were obvious in this case. However, column names can be rather ambiguous when it comes to explaining the meaning of data, such as:
SELECT lstprc, prdnm FROM long_table_name;
You can use a column alias instead of the column name in the query results to better describe the data displayed in the output.
For example, instead of using first name and last name for employee display names, you might wish to use forename and surname.
Simply list the column alias next to the column name in the SELECT clause to tell Oracle to utilise it, as illustrated below:
SELECT first_name AS forename, last_name AS surname FROM employees;
To distinguish between the column name and the column alias, use the AS keyword. Because the AS keyword is optional, you can use the following syntax to avoid it:
SELECT first_name forename, last_name surname FROM employees;
Oracle capitalises the column heading in the query result by default. You must enclose the column heading in quotation marks (“”) if you want to modify the letter case.
You don’t need to wrap the column alias in quotation marks if it only contains one word with no special symbols, such as a space. Otherwise, you’ll get an error until you enclose the column heading in quotation marks.
Have a look at the below PL/SQL query
SELECT first_name "First Name", last_name "Family Name" FROM employees;
We utilised the column aliases “First Name” and “Family Name” wrapped in quotation marks in this example. Oracle will give you the following error if you remove the quote marks:
ORA-00923: FROM keyword not found where expected
You can use the column alias for an expression in addition to make the column heads more relevant, for example:
SELECT first_name || ' ' || last_name FROM employees;
To create the whole name, we concatenated the first name, space, and last name in the above query. Oracle utilised an equation for the column heading, as displayed in the output, which was not clear.
The expression in the following query is represented as a column alias:
SELECT first_name || ' ' || last_name AS "Full Name" FROM employees;
To sort the result set, utilise the column alias in the ORDER BY clause. Consider the following scenario:
SELECT product_name, list_price - standard_cost AS gross_profit FROM products ORDER BY gross_profit DESC;
Table Alias in Oracle
A table name can be given an alias, just as a column name. A table alias is a query’s temporary name for a table. With or without the AS keyword, you can define a table alias after the table name:
table_name AS table_alias table_name table_alias
If you don’t have a table alias, you can qualify a column with the following syntax:
A table alias increases the query’s readability and minimises the amount of keystrokes required.
SELECT e.first_name employee, m.first_name manager FROM employees e INNER JOIN employees m ON m.employee_id = e.employee_id;
The employees table in this case joins to itself. Self-join is the name for this technique. You must utilise the table alias to give the employees two separate names, e for employees and m for managers, because a table can only appear in a query once.