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.
