Oracle / PLSQL: ALIASES in Oracle

This article is written about how to use Oracle ALIASES (temporary names for columns or tables) with syntax and examples.

Description

Oracle ALIASES can be used to create a temporary title for columns or tables.

COLUMN ALIASES are used to make column headings in your end result set less complicated to read. TABLE ALIASES are used to shorten your SQL to make it less difficult to study or when you are performing a self be part of (ie: record the identical table greater than once in the FROM clause).

Syntax

The syntax to ALIAS A COLUMN in Oracle/PLSQL is:

column_name AS alias_name

OR

The syntax to ALIAS A TABLE in Oracle/PLSQL is:

table_name alias_name

Parameters or Arguments

column_name The original name of the column that you want to alias. table_name The unique identify of the desk that you desire to alias. alias_name The brief title to assign.

Note

If the alias_name includes spaces, you must enclose the alias_name in quotes. It is suited to use spaces when you are aliasing a column name. However, it is not generally true exercise to use spaces when you are aliasing a table name. The alias_name is solely legitimate within the scope of the SQL statement.

Example – ALIAS a column

Generally, aliases are used to make the column headings in your result set easier to read. For example, when concatenating fields together, you may alias the result.

For example:

SELECT contact_id, first_name || last_name AS NAME
FROM contacts
WHERE last_name = 'Anderson';

In this example, we have aliased the 2nd column (ie: first_name and last_name concatenated) as NAME. As a result, NAME will display as the heading for the 2nd column when the result set is returned. Because our alias_name did no longer encompass any spaces, we are no longer required to enclose the alias_name in quotes.

However, it would have been flawlessly perfect to write this example the use of fees as follows:

SELECT contact_id, first_name || last_name AS "NAME"
FROM contacts
WHERE last_name = 'Anderson';

Next, let’s seem to be at an example where we are required to enclose the alias_name in quotes.

For example:

SELECT contact_id, first_name || last_name AS "CONTACT NAME"
FROM contacts
WHERE last_name = 'Anderson';

In this example, we have aliased the 2d column (ie: first_name and last_name concatenated) as “CONTACT NAME”. Since there are spaces in this alias_name, “CONTACT NAME” need to be enclosed in quotes.

Example – ALIAS a Table

When you create an alias on a table, it is both because you format to list the equal table name extra than once in the FROM clause (ie: self join), or you prefer to shorten the desk title to make the SQL assertion shorter and less difficult to read.

Let’s seem at an instance of how to alias a desk name in Oracle/PLSQL.

For example:

SELECT p.product_id, p.product_name, categories.category_name
FROM products p
INNER JOIN categories
ON p.category_id = categories.category_id
ORDER BY p.product_name ASC, categories.category_name ASC;

In this example, we’ve created an alias for the merchandise table called p. Now inside this SQL statement, we can refer to the products table as p.

When developing desk aliases, it is now not imperative to create aliases for all of the tables listed in the FROM clause. You can select to create aliases on any or all of the tables.

For example, we should adjust our instance above and create an alias for the classes desk as well.

SELECT p.product_id, p.product_name, c.category_name
FROM products p
INNER JOIN categories c
ON p.category_id = c.category_id
ORDER BY p.product_name ASC, c.category_name ASC;

Now we have an alias for classes desk referred to as c as well as the alias for the merchandise table known as p.