How do I show ALIASES in Oracle? & How do ALIASES work?

ALIASES in Oracle

In Oracle, an alias is a short-term, alternate name that is used to refer to an element somewhere else in the same SQL statement. This element is typically a column or table. Especially when working with intricate joins or expressions, this method improves the readability and clarity of SQL queries.

Aliases are primarily used as temporary query aliases, also referred to as correlation names or column aliases, however the idea behind an alias also applies to permanent database item naming using synonyms.

Column Aliases

As a new heading for the output, a column alias is a name given to an expression in the SELECT list. This is especially helpful when the data being shown is produced from a function or formula, which might otherwise produce an output with a complicated or confusing column name. For as long as that particular query is running, the alias essentially renames the select list item.

Syntax and Rules for Column Aliases

When assigning a column alias in Oracle SQL, you can utilise the optional AS keyword to keep the original definition and its new name separate.

Implicit Aliasing and Case: Oracle automatically recognises an alias as a single word typed outside of quote marks, after the column or expression. Oracle converts this one-word alias into uppercase regardless of the typing style when it is used without quotations. Comparing the output of a DESCRIBE command to the column names in a CREATE VIEW statement makes this clear.
Quoting and Case Sensitivity: You must wrap the alias in double quote marks ("") if it contains spaces or special characters, or if you need the alias to maintain mixed case. However, when constructing database objects like views, it is typically not recommended to use double quotation marks in aliases because this can compel the column name to be stored in mixed case internally, making it difficult to do future queries unless the alias is quoted each time around. Aliases should be kept “naked” (without quotes) for normal usage.
Usage: The ORDER BY clause of a query may make reference to column aliases, although other clauses such as WHERE usually do not.

Creating a Column Alias

To track the values of the data, we begin by making a basic table.

-- CREATE TABLE: Create a table to store monthly metrics.
CREATE TABLE MONTHLY_DATA (
    MonthID        NUMBER(2),
    SalesAmount    NUMBER(10, 2),
    TaxRate        NUMBER(4, 2)
);

-- INSERT values: Populate the table with sample data.
INSERT INTO MONTHLY_DATA VALUES (1, 1500.00, 0.05);
INSERT INTO MONTHLY_DATA VALUES (2, 2200.50, 0.05);
COMMIT;

-- SELECT with Column Aliases: Calculate tax payable and total sales.
SELECT
    MonthID,
    SalesAmount * TaxRate AS TaxPayable,  -- Alias using AS keyword 
    SalesAmount * (1 + TaxRate) TotalSales -- Alias without AS keyword 
FROM MONTHLY_DATA
WHERE MonthID = 1;

-- Output:
--    MONTHID  TAXPAYABLE TOTALSALES
-- ---------- ---------- ----------
--          1      75.00    1575.00

In this example, the output heading’s complicated calculation strings are replaced with comprehensible column aliases (TaxPayable and TotalSales) for the derived columns (derived from mathematical expressions).

Table Aliases

A temporary name given to a table or view in the FROM clause of a SQL query is called a table alias, sometimes referred to as a correlation name.

Purpose of Table Aliases

Table aliases are used for two main purposes:

Resolving Ambiguity in Joins: Oracle requires explicit qualification to determine which column belongs to which table when querying from two or more tables that share identically named columns. The column name is confusing, according to Oracle, if it is not qualified (e.g., WEATHER.City or LOCATION.City). To qualify columns (e.g., W.City), use short table aliases (e.g., W for WEATHER and L for LOCATION).
Simplifying Query Length: Aliases greatly shorten and simplify SQL statements, particularly those that contain a lot of joins or self-joins.
Self-JOins: When a table is joined back to itself to create associations inside the same data structure, table aliases are required.

Using Table Aliases for Ambiguity and Clarity

To show how important table aliases are for resolving ambiguity, we make two tables, T1 and T2, with the column name MetricValue in common, and join them using a shared ID.

-- CREATE TABLE: Create two tables that share a column name 'MetricValue'.
CREATE TABLE Sales_Metrics (
    SalesID NUMBER(5),
    MetricValue VARCHAR2(10)
);

CREATE TABLE Target_Metrics (
    TargetRefID NUMBER(5),
    MetricValue VARCHAR2(10) -- Ambiguous column name
);

-- INSERT values: Populate tables.
INSERT INTO Sales_Metrics VALUES (10, 'Actual');
INSERT INTO Target_Metrics VALUES (10, 'Goal');
COMMIT;

-- SELECT with Table Aliases: Querying both MetricValue columns requires aliases
-- to distinguish them, which in turn necessitates qualifying the columns in the SELECT list.
SELECT
    s.SalesID,
    s.MetricValue AS Actual, -- Using table alias 's' for Sales_Metrics
    t.MetricValue AS Target  -- Using table alias 't' for Target_Metrics
FROM
    Sales_Metrics s, -- Assigning table alias 's' 
    Target_Metrics t  -- Assigning table alias 't'
WHERE
    s.SalesID = t.TargetRefID;

-- Output:
--    SALESID ACTUAL     TARGET
-- ---------- ---------- ----------
--         10 Actual     Goal

The SELECT statement’s columns would be unclear if they were written as MetricValue, MetricValue without the table aliases (s. and t.). As a result, the database would produce an error.

Other Forms of Oracle Aliases

Additionally, Oracle uses “alias” to refer to networking configurations and persistent objects:

Synonyms (Permanent Aliases): Synonyms, sometimes known as permanent aliases, are named database objects that can be used as other names or aliases for other objects, including stored programs, tables, views, or sequences. Unlike correlation names, synonyms are permanent and remain in the data dictionary. Location transparency, which conceals an object’s actual location, frequently across database links, and data independence, which enables applications to access objects without knowing the owner’s schema name, are two essential features. Private synonyms are solely useable by the creator, whereas public synonyms are accessible to anybody with a database connection.
Net Service Aliases: To make the connection string needed to access a remote database instance shorter, alternate names set in configuration files (such as tnsnames.ora) are used. After the alias (such as ASM) is specified in the tnsnames.ora file, the whole connection description (protocol, host, port, and service name) is usually included.
Extract Aliases: When introducing an Extract group in systems such as Oracle GoldenGate, it is possible to specify an Extract alias.

In conclusion, aliasing is a key element of the Oracle environment that enhances clarity, conciseness, and system flexibility, whether it is used as a transient column label, a correlation name in intricate queries, or a persistent object name via a synonym.

Page Content

Tutorials