Reference alias (calculated in SELECT) in WHERE clause
You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated. Two workarounds:
SELECT BalanceDue FROM (
SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
) AS x
WHERE BalanceDue > 0;
Or just repeat the expression:
SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE (InvoiceTotal - PaymentTotal - CreditTotal) > 0;
I prefer the latter. If the expression is extremely complex (or costly to calculate) you should probably consider a computed column (and perhaps persisted) instead, especially if a lot of queries refer to this same expression.
PS your fears seem unfounded. In this simple example at least, SQL Server is smart enough to only perform the calculation once, even though you've referenced it twice. Go ahead and compare the plans; you'll see they're identical. If you have a more complex case where you see the expression evaluated multiple times, please post the more complex query and the plans.
Here are 5 example queries that all yield the exact same execution plan:
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE LEN(name) + column_id > 30;
SELECT x FROM (
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE column_id + LEN(name) > 30;
SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;
SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE LEN(name) + column_id > 30;
Resulting plan for all five queries:
Reference an alias elsewhere in the SELECT list
You can't refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. Typical workaround is to bury it in a derived table:
SELECT
FirstName, LastName, Other,
Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END
FROM
(
SELECT FirstName, LastName,
CASE WHEN LastName = 'Jones'
THEN 'N/A'
END AS Other
FROM dbo.table_name
) AS x;
How to use the alias of my calculated parameter (in SELECT clause) in WHERE clause with PostgreSQL to prevent repetition?
You can't reuse a select
alias in the where
clause. You need to repeat the expression, or use a subquery or cte.
For what it's worth, you can use between
to have the expression just once instead of twice in the where
clause:
SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM public.foo f
WHERE date_part('year', CURRENT_DATE) - f.birth_year BETWEEN 20 AND 30
As for the subquery solution, that would be:
SELECT *
FROM (
SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM public.foo
) f
WHERE age BETWEEN 20 AND 30
How do I use alias in where clause?
The SQL-Server docs says:
column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.
Similar in the MySQL doc it says:
Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.
In MySQL you can at least reuse aliases in the SELECT clause
Referring to a Column Alias in a WHERE Clause
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)
Normally you can't refer to field aliases in the WHERE
clause. (Think of it as the entire SELECT
including aliases, is applied after the WHERE
clause.)
But, as mentioned in other answers, you can force SQL to treat SELECT
to be handled before the WHERE
clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):
Parenthesis/Subselect:
SELECT
*
FROM
(
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
) as innerTable
WHERE daysdiff > 120
Or see Adam's answer for a CTE version of the same.
Reference an alias for an expression in a SELECT statement
You can't. But SQL Server has a very nifty feature where you can define the column in the from clause using apply
:
SELECT v.newdate,
Upper(LEFT(Rtrim(t1.[NameColumn4]),19) + v.newdate + RIGHT(t1.[NameColumn5], 3)) AS newname
FROM table1 t1 CROSS APPLY
(VALUES ( ) v(newdate)
How to use aliases in WHERE clause?
Logically, WHERE
clause is calculated before SELECT
. That's why you cannot use column sal
in WHERE
. It's not like in other languages where code is executed from top to bottom. A simplified order looks like this:
- FROM
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
You can find more information about it by typing logical query processing
in your browser.
Reuse calculated column in WHERE clause
There is no way to reuse the calculated field on the same level SELECT
. You will need to nest it in order to use the alias.
SELECT field1
, calc_field
FROM (
SELECT field1
, CONCAT (field2, field3) AS calc_field
FROM MyTable
) tbl
WHERE calc_field LIKE 'A%'
This is because of the order in which clauses are executed in a SQL query. As you can see in the way the clauses are listed, the SELECT
clause, where the alias is generated, is executed after the WHERE
clause.
Thus, the alias is not "visible" in the WHERE
clause, because the alias is generated after the WHERE
is applied.
How to use alias column name in where clause in SQL Server
You can't use aliased columns in a WHERE
clause. You can try using a derived table. Perhaps something like this (sorry, not tested):
SELECT * FROM
(SELECT SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192
AS DistanceFromAddress from tblProgram) mytable
WHERE DistanceFromAddress < 2
Related Topics
Querying Data by Joining Two Tables in Two Database on Different Servers
Generate Dates Between Date Ranges
Where Is |Datadirectory| Defined
Get Join Table as Array of Results With Postgresql/Nodejs
Group by Behavior When No Aggregate Functions Are Present in the Select Clause
SQL Query - Using Order by in Union
How to Find Duplicate Values in a Table in Oracle
How to Rewrite Is Distinct from and Is Not Distinct from in SQL Server 20008R2
Alter a MySQL Column to Be Auto_Increment
SQL Performance on Left Outer Join VS Not Exists
SQL Query to Insert Datetime in SQL Server
How to Pass Variable as a Parameter in Execute SQL Task Ssis
Postgres Not Allowing Localhost But Works with 127.0.0.1
MySQL :: Insert into Table, Data from Another Table
Using Isnull VS Using Coalesce for Checking a Specific Condition
How to Capitalize the First Letter of Each Word in a String in SQL Server