Oracle SQL ALIAS

Oracle SQL ALIAS

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 4 mins read

In this Oracle tutorial, syntax and examples are provided to explain how to use Oracle SQL ALIAS (temporary names for columns or tables).

Description

ALIASES in Oracle can be used to give a column or table a temporary name.

  • You can use COLUMN ALIASES to improve the readability of column headings in your results.
  • Shortening your SQL and performing a self-join are two situations where TABLE ALIASES come in handy.

Syntax

Oracle SQL uses the following syntax to ALIAS A COLUMN:

column_name AS alias_name

OR

The syntax for ALIASING A TABLE in Oracle SQL is:

table_name alias_name

Parameters

column name A list of the original names of the columns you want to alias. Table name An alias for the table's original name. Alias name What should be given a temporary name?

Note

  • The alias name must be enclosed in quotes if it has any spaces in it.
  • The use of spaces in the aliasing of a column's name is permitted. Using spaces in an aliased table name, on the other hand, is not recommended.
  • In the SQL statement, the alias name can only be used.

Example: ALIAS a column

Most of the time, aliases are used to make it easier to read the column headings in your result set. For example, you could alias the result of putting fields together by adding them together.

For example:

SELECT contact_id, f_name || l_name AS NAME
FROM contacts
WHERE l_name = 'John';

In this example, the second column, which is made up of a first name and last name joined together, is called NAME. So, when the result set is given back, NAME will be the heading for the second column. Because our alias name didn't have any spaces in it, we don't have to put it in quotes.

But it would have been fine to write this example using quotes in the following way:

SELECT contact_id, f_name || l_name AS "NAME"
FROM contacts
WHERE l_name = 'John';

Next, let's consider the example where the alias name must be enclosed in quotes.

For example:

SELECT contact_id, f_name || l_name AS "FULL NAME"
FROM contacts
WHERE l_name = 'John';

"FULL NAME" has been used as an alias for the second column (first name and last name combined). 'FULL NAME' must be enclosed in quotation marks because this alias name contains spaces.

Example: ALIAS a Table

A table alias is usually created to shorten a long table name in the FROM clause, or to make the SQL statement easier to read if you plan to use the table name more than once in the FROM clause (e.g., self join).

An example of aliasing a table name in Oracle SQL is shown here.

Oracle Alias in Select Statement:

SELECT p.prod_id, p.prod_name, categories.categ_name
FROM products p
INNER JOIN categories
ON p.categ_id = categories.category_id
ORDER BY p.prod_name ASC, categories.categ_name ASC;

An alias for the products table, called p, has been set up in this example. We can now refer to the products table as p. in this SQL statement.

It is not necessary to make aliases for all of the tables in the FROM clause when making aliases for tables. You can make aliases for any or all of the tables if you want to.

For example, we could modify our previous example to include an alias for the categories table.

Oracle Alias in WHERE Clause:

SELECT p.prod_id, p.prod_name, c.categ_name
FROM products p
INNER JOIN categories c
ON p.categ_id = c.category_id
ORDER BY p.prod_name ASC, c.categ_name ASC;

We now have a table alias for categories (c) and a table alias for products (p).