Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.
 
SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.
 

REQUEST COMPLIMENTARY SQLS*PLUS LICENCE

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

Oracle Aliases

17 August 2020

Oracle Aliases
In this tutorial you will learn how to use Oracle ALIASES (aliases for columns or tables) with syntax and examples.

Oracle ALIASES can be used to create an alias for a column or table.

ALIASES columns are used to simplify the reading of columns in your resulting set.

ALIASES tables are used to shorten your SQL code to make it easier to read or when you make a standalone connection (i.e.: enumerating the same table more than once in the FROM sentence).

Syntax for ALIAS columns in Oracle / PLSQL

column_name AS alias_name

OR 

Syntax for ALIAS tables in Oracle / PLSQL

table_name alias_name

Parameters or arguments

  • column_name – the original name of the column to which you want to specify an alias.
  • table_name – the initial name of the table to which you want to specify an alias.
  • alias_name – the nickname for the destination.

Note: If alias_name contains spaces, you must quote alias_name.

Example of ALIAS as a column

Typically, aliases are used to simplify the reading of column headers in your resulting set. For example, when fields are concatenated, you may get the following result.

For example:

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

In this example, we replaced the second column (that is: first_name and last_name concatenated) as NAME. The result is that the NAME will appear as the header for the second column when the resulting set is returned.

Since our alias_name contains no spaces, we do not have to wrap alias_name in quotes.

However, it would be acceptable to write this example using quotes as follows:

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

Then let’s take a look at an example where we should quote alias_name.

For instance:

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

In this example, we changed the second column (i.e.: first_name and last_name were concatenated) to “CONTACT NAME”. Since there are spaces in this alias, “CONTACT NAME” must be enclosed in quotes.

Example ALIAS table

When you create an alias for a table, this is either because you plan to list the same table name more than once in the FROM sentence (i.e.: join), or you want to shorten the table name to make the SQL statement shorter and easier to read.

Let’s take the example of ALIAS table 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 created an alias for the products p. table. In this SQL instruction, we can now refer to the products table as p.

When creating table aliases, there is no need to create aliases for all tables listed in the FROM sentence. You can create aliases for any or all tables.

For example, we could change our example above and create an alias for the table in this way.

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;

We now have an alias for the categories c table, and an alias for the products p table.

What are Table Aliases? (Introduction to Oracle SQL)

 
Tags: , , , , ,

MORE NEWS

 

Preamble​​NoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...

Preamble​​MongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...

5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...

The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...

Preamble​​MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...

First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...

Preamble​​Atom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...

Preamble​​MongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...

FlexibilityOne of the most advertised features of MongoDB is its flexibility.  Flexibility, however, is a double-edged sword. More flexibility means more...

Preamble​​SQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...

Preamble​​Writing an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...

Preamble​​Oracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...

Preamble​​IBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...

  What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....

Preamble​​If you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...

Preamble​​Starting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...