The ALIAS Clause: Reusing Column Aliases Within the Same Query

A subtle but genuinely useful SQL improvement in Oracle 23ai is the ALIAS clause — the ability to define a column alias once and reuse it in other parts of the same query, without repeating the expression.

The problem it solves

In classic SQL, you can’t reference a column alias from the SELECT list in the WHERE clause or the GROUP BY clause of the same query. This leads to expression repetition:

-- Classic SQL: expression repeated three times
SELECT employee_id,
       salary * 12 AS annual_salary,
       TRUNC(salary * 12 / 1000) AS salary_band
FROM   employees
WHERE  salary * 12 > 50000     -- repeated expression
ORDER BY salary * 12 DESC;    -- repeated again

With the ALIAS clause (Oracle 23ai):

SELECT employee_id,
       salary * 12 AS annual_salary    ALIAS annual_salary,
       TRUNC(annual_salary / 1000)     AS salary_band
FROM   employees
WHERE  annual_salary > 50000
ORDER BY annual_salary DESC;

The alias annual_salary defined in the first SELECT expression can be referenced by subsequent expressions in the same SELECT list, in WHERE, GROUP BY, HAVING, and ORDER BY.

Practical analytics example:

SELECT
    region,
    SUM(revenue) AS total_revenue   ALIAS total_revenue,
    SUM(cost)    AS total_cost      ALIAS total_cost,
    total_revenue - total_cost      AS gross_profit,
    ROUND(gross_profit / total_revenue * 100, 2) AS margin_pct
FROM sales
GROUP BY region
HAVING gross_profit > 0
ORDER BY margin_pct DESC;

Clean, readable, no repeated expressions. This is exactly the kind of ergonomic improvement that makes SQL more maintainable at scale.

Author: matheusdba

Discover more from grepOra