Summary: in this tutorial, you will learn how to use Oracle alias including column and table aliases to make the heading of the output more meaningful and to improve readability of a query.
Oracle column alias
When you query data from a table, Oracle uses the column names of the table for displaying the column heading. For example, the following statement returns the first name and last name of employees:
SELECT first_name, last_name FROM employees ORDER BY first_name;
In this example,
last_name column names were quite clear. However, sometimes, the column names are quite vague for describing the meaning of data such as:
SELECT lstprc, prdnm FROM long_table_name;
To better describe the data displayed in the output, you can substitute a column alias for the column name in the query results.
For instance, instead of using
last_name, you might want to use forename and surname for display names of employees.
To instruct Oracle to use a column alias, you simply list the column alias next to the column name in the
SELECT clause as shown below:
SELECT first_name AS forename, last_name AS surname FROM employees;
AS keyword is used to distinguish between the column name and the column alias. Because the
AS keyword is optional, you can skip it as follows:
SELECT first_name forename, last_name surname FROM employees;
Using Oracle column alias to make column heading more meaningful
By default, Oracle capitalizes the column heading in the query result. If you want to change the letter case of the column heading, you need to enclose it in quotation marks (“”).
SELECT first_name "Forename", last_name "Surname" FROM employees;
As shown in the output, the forename and surname column headings retain their letter cases.
If the column alias consists of only one word without special symbols like space, you don’t need to enclose it in quotation marks. Otherwise, you must enclose the column heading in quotation marks or you will get an error.
See the following query:
SELECT first_name "First Name", last_name "Family Name" FROM employees;
In this example, we used the column aliases
"First Name" and
"Family Name" enclosed in quotation marks. If you remove the quotation marks, Oracle will issue the following error:
ORA-00923: FROM keyword not found where expected
Using Oracle column alias for expression
Besides making the column headings more meaningful, you can use the column alias for an expression, for example:
SELECT first_name || ' ' || last_name FROM employees;
In the above query, we concatenated the first name, space, and the last name to construct the full name. As shown in the output, Oracle used the expression for the column heading which was not intuitive.
The following query uses a column alias for the expression:
SELECT first_name || ' ' || last_name AS "Full Name" FROM employees;
Similarly, the following statement uses a column alias for gross profit calculation expression:
SELECT product_name, list_price - standard_cost AS gross_profit FROM products;
Using Oracle column alias with
ORDER BY clause
You can use the column alias in the
ORDER BY clause to sort the result set. See the following example:
SELECT product_name, list_price - standard_cost AS gross_profit FROM products ORDER BY gross_profit DESC;
Oracle table alias
Similar to a column name, you can assign a table name an alias. A table alias is a temporary name for a table in a query. You specify a table alias after the table name either with or without the
table_name AS table_alias table_name table_alias
Without the table alias, you qualify a column by using the following form:
However, you must use an alias instead of the table name after you assign the table a table alias:
A table alias improves the readability of the query and reduces the number of keystrokes.
SELECT e.first_name employee, m.first_name manager FROM employees e INNER JOIN employees m ON m.employee_id = e.employee_id;
In this example, the
employees table joins to itself. This technique is called self-join. Because a table only can appear in a query once, you must use the table alias to give the employees two different names,
e for employees and
m for managers.
In this tutorial, you have learned how to use the Oracle alias including column and table aliases in SQL queries.