Oracle 26ai ALIAS Clause: Powerful Query Patterns

The ALIAS clause enables some surprisingly elegant query patterns. Let’s look at the cases where it makes the biggest difference.

Multi-step financial calculations:

SELECT
    order_id,
    quantity * unit_price                      AS gross_amount    ALIAS gross_amount,
    gross_amount * tax_rate / 100              AS tax_amount       ALIAS tax_amount,
    gross_amount + tax_amount                  AS total_amount     ALIAS total_amount,
    total_amount * discount_pct / 100          AS discount_amount  ALIAS discount_amount,
    total_amount - discount_amount             AS net_payable
FROM order_lines
WHERE gross_amount > 1000;

Without ALIAS, you’d either repeat the expressions (error-prone) or use a subquery to materialize intermediate results. Now it’s a single, readable SELECT.

Analytics with computed breakpoints:

SELECT
    customer_id,
    SUM(order_value)                          AS lifetime_value   ALIAS lifetime_value,
    CASE WHEN lifetime_value >= 10000 THEN 'PLATINUM'
         WHEN lifetime_value >= 5000  THEN 'GOLD'
         WHEN lifetime_value >= 1000  THEN 'SILVER'
         ELSE 'BRONZE'
    END                                       AS customer_tier    ALIAS customer_tier,
    COUNT(*) FILTER (WHERE order_date >= ADD_MONTHS(SYSDATE, -12)) AS orders_last_year
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

ALIAS in WHERE clause:

SELECT
    employee_id,
    salary * (1 + bonus_pct/100)              AS total_comp  ALIAS total_comp,
    TRUNC(total_comp / 1000) * 1000           AS comp_band
FROM employees
WHERE total_comp > 60000    -- references the alias directly
ORDER BY total_comp DESC;

Previously this WHERE clause would require a subquery or repeating the expression. ALIAS makes the intent clear and the query maintainable.

Author: matheusdba