Reference alias (calculated in SELECT) in WHERE clause

Efficient ways to handle Aliases

Using CTEs/Subqueries for easy maintenance and readability

The usage of subqueries or CTEs promotes ease of maintenance and readability. This separation of concerns clearly highlights the calculated fields, allowing for ready references in following clauses including WHERE or HAVING.

Performance consideration

When handling aliases, performance must always be uppermost. Investigate the execution plan and evade unnecessary calculations. Depending on your SQL engine, persistent computed columns or indexes on expressions could be the optimal solution if the calculated aliases are frequently invoked.

APPLY: CROSS and OUTER

APPLY operator in SQL Server is a handy tool. Both CROSS APPLY and OUTER APPLY can be strategically deployed to reuse complicated arithmetic operations in SELECT and WHERE clauses without writing the operation twice, essentially acting like a "repeat this, but don't repeat yourself".

SELECT t.col1, t.col2, ca.result
FROM my_table t
CROSS APPLY (SELECT (t.col1 * t.col2) AS result) ca
WHERE ca.result > 100; -- so, this is both efficient *and* lazy, my two favorite things


In particular, the OUTER APPLY function excels at handling NULL values gracefully.

Aggregation and aliases

In cases where aggregation of data is required, calculated aliases can be filtered using the HAVING clause:

SELECT employee_id, SUM(sales) AS total_sales
FROM sales_records
GROUP BY employee_id
HAVING total_sales > 10000; -- High sales only club, no scrubs allowed


Advanced techniques for handling computed aliases

Temporary storage for subsequent use

Variables or temporary tables serve as good temporary storage for cumbersome and complex operations in multi-step queries, enhancing readability and maintainability.

Employing the RIGHT join strategy

Depending on the specific use-case, different join strategies, including CROSS JOIN for independent operations, can be beneficial. When you're dealing with groupings, GROUP BY operates in tandem with SELECT computed columns allowing subsequent filtering in the HAVING clause.

Dealing with complex queries

For involved calculations, inline views, or table-valued functions can be used to encapsulate the logic, providing a smooth and modular approach.

Handling NULL values

When dealing with nullable columns, OUTER APPLY shines. This technique assures that calculated fields are at hand without discarding rows due to null calculations.