Alias in Oracle

Posted by AbundantCode

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:

table_name.column_name

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.

Tags: