Oracle Alias

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;
Code language: SQL (Structured Query Language) (sql)
Oracle Alias - employee list

In this example, first_name and 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 first_name and 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;
Code language: PHP (php)
Oracle Alias - column alias example

The 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;
Code language: JavaScript (javascript)
Oracle Alias - column alias with quotation marks

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;
Code language: JavaScript (javascript)
Oracle Alias - column alias with spaces

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;
Code language: JavaScript (javascript)
Oracle Alias - column alias for expression

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;
Code language: PHP (php)

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;
Code language: SQL (Structured Query Language) (sql)
Oracle Alias - column alias for expression example

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;
Code language: PHP (php)
Oracle Alias - column alias in order by clause

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 AS keyword:

table_name AS table_alias table_name table_alias
Code language: PHP (php)

Without the table alias, you qualify a column by using the following form:

table_name.column_name
Code language: CSS (css)

However, you must use an alias instead of the table name after you assign the table a table alias:

table_alias.column_name
Code language: CSS (css)

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;
Oracle Alias - table alias example

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.

Was this tutorial helpful?