Can I Use Calculated Aliases in SQL WHERE Clauses?

Using computed aliases in SQL WHERE clauses

When using computed aliases in SQL, it is important to understand the limitations of using them in the WHERE clause. As shown in the query below:

<code class="language-sql">SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE BalanceDue > 0 --错误</code>

Computed valueBalanceDue as an alias cannot be used directly in the WHERE clause. However, there are ways to achieve the desired results.

One way is to use a derived table:

<code class="language-sql">SELECT BalanceDue
FROM (
  SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
  FROM Invoices
) AS x
WHERE BalanceDue > 0;</code>

Alternatively, the calculation can be repeated in the WHERE clause:

<code class="language-sql">SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE (InvoiceTotal - PaymentTotal - CreditTotal) > 0;</code>

In most cases, it is recommended to repeat expressions in the WHERE clause for the sake of simplicity.

Keep in mind that overuse of computed aliases in complex queries may cause performance issues. In this case, consider creating a computed column or persisting the results for faster access.

Interestingly, in the given example, SQL Server optimizes the query to only perform the calculation once, no matter how the alias is referenced. However, when working with more complex queries, it's important to understand the potential performance impact.

The above is the detailed content of Can I Use Calculated Aliases in SQL WHERE Clauses?. For more information, please follow other related articles on the PHP Chinese website!