Oracle Column Aliases

This SQL tutorial focuses on the SQL Column Aliases in Oracle, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to write basic queries in Oracle. To read additional posts regarding this subject, please use the following links:

• Writing Basic SQL SELECT statements
• SQL Arithmetic Operators
• SQL Column Aliases
• SQL String Concatenation
• SQL DISTINCT keyword

Oracle Column Aliases

In Oracle by default the column’s heading in the results produced by your query, is the original column’s name as it was defined in the table.

SELECT product_name
FROM products

product_name
----------
Coffee
Tea

SELECT 'Hello ' + product_name
FROM products

'Hello' + Product name
----------------------
Hello Tea
Hello Coffee

Oracle Column Aliases are used to change column’s heading. Using the Oracle Column Aliases the headings will be more easy to understand and more meaningful. To create an Oracle Column Alias use the following syntax :

SELECT column_name AS "Column_Alias"
FROM table_name

For Example :

SELECT product_name, unit_price, unit_price  + 500  AS "NEW_UNIT_PRICE"
FROM products

The result:

product_name   unit_price    NEW_unit_price
---------      ----------    ------------------
Tea            30            530

You can also write the Oracle column’s alias without the AS Keyword, for example:

SELECT product_name, unit_price, unit_price  + 500  "NEW_UNIT_PRICE"
FROM products

The result:

product_name   unit_price   NEW_UNIT_PRICE
---------      -----------  ------------------
Tea            30           530

SELECT product_name, unit_price, unit_price  + 500 new_unit_price
FROM products

The result:

product_name    unit_price  NEW_UNIT_PRICE
-----------     ---------   ------------------
Tea            30          530

• When an Oracle column alias is written without enclosing double quotes (” “), it is not possible to write the alias as two (or more) separate words; you will have to insert some character between these words, underline (_) is most commonly used in these cases.

-- This query will generate an error
-- The "New Unit Price" column alias should be enclosed within double quotes
SELECT product_name, unit_price, unit_price * 1.1 AS New Unit Price
FROM products

-- Insert an underline between these words :
SELECT product_name, unit_price, unit_price * 1.1 AS New_Unit_Price
FROM products

-- Or use double quotes :
SELECT product_name, unit_price, unit_price * 1.1 AS "New Unit Price"
FROM products

• In order to make your Oracle query more readable, always use the AS keyword, always use double quotes (” “), and when your alias consists of two words or more, always ensure that you insert an underline between them.
• Another common approach to handle an alias consists of two words or more, is to concatenate the words, and capitalize the first letter of each word  :

SELECT product_name, unit_price, unit_price  + 500 AS 'NewUnitPrice'
FROM products