Referring to a Column Alias in a WHERE Clause

Question

SELECT logcount, logUserID, maxlogtm
   , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE daysdiff > 120

I get

"invalid column name daysdiff".

Maxlogtm is a datetime field. It's the little stuff that drives me crazy.

Fatten

not sure for mysql, but maybe the alias needs to be wrapped in ticks `daysdiff`. – Scarcely

Answer 1

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.

Misbelief

This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain. REFER - #357175 – Dowitcher

afaik if the alias in the select is a correlated subquery this will work while the CTE solution won't. – Atonal

As Pascal mentioned in his answer here https://mcmap.net/q/109421/-referring-to-a-column-alias-in-a-where-clause, you can use HAVING clause which seems to work faster than subqueries. – Hegyera

@Hegyera The HAVING answer doesn't work in most SQL environments, including MS-SQL which this question is about. (In T-SQL, HAVING requires an aggregate function.) – Misbelief

I never knew you couldn't reference aliases until I came across this issue just now. Love the workaround... does this have any major performance implications? – Garish

A subquery in the FROM clause is called derived table. – Diatropism

@Mr.Boy This shouldn't have significant performance implications. SQL should be able to optimize and pull the outer where into the plan appropriately. But with SQL performance, it's always good to test. – Misbelief

Answer 2

If you want to use the alias in your WHERE clause, you need to wrap it in a sub select, or CTE:

WITH LogDateDiff AS
(
   SELECT logcount, logUserID, maxlogtm
      , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary
)
SELECT logCount, logUserId, maxlogtm, daysdiff
FROM LogDateDiff
WHERE daysdiff > 120

Lanate

Do you happen to know how this fairs efficiency wise? Is there extra overhead using a CTE? – Optometer

A CTE is just prettier syntax for a sub-query, so the performance would be similar to that. In my experience, the performance difference has not been something that has concerned me for operations like this, but it should be fairly simple to test it in your environment to see if your specific table/query is adversely affected with this vs. calling out the formula specifically in the where clause. I suspect you will not notice a difference. – Lanate

CTEs are super nice until you try to use one as a subquery. i've had to resort to creating them as views to nest them. i consider this a serious SQL shortcoming – Abuzz

Answer 3

The most effective way to do it without repeating your code is use of HAVING instead of WHERE

SELECT logcount, logUserID, maxlogtm
   , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
HAVING daysdiff > 120

Cortez

I think using HAVING on aliases is not standard (it does works on MySQL, though). Specifically, I think it does not work with SQL Server. – Snaggletooth

SQL Server: [S0001][207] Invalid column name 'daysdiff' – Chapfallen

SQL Server: [S0001][8121] Column 'day' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. – Chapfallen

@Chapfallen - I just get the error Invalid column name '<name>'. Either way, it doesn't work in SQL Server... – Armbrecht

Answer 4

If you don't want to list all your columns in CTE, another way to do this would be to use outer apply:

select
    s.logcount, s.logUserID, s.maxlogtm,
    a.daysdiff
from statslogsummary as s
    outer apply (select datediff(day, s.maxlogtm, getdate()) as daysdiff) as a
where a.daysdiff > 120

Socio

Answer 5

How about using a subquery(this worked for me in Mysql)?

SELECT * from (SELECT logcount, logUserID, maxlogtm
   , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary) as 'your_alias'
WHERE daysdiff > 120

Cheltenham

Answer 6

HAVING works in MySQL according to documentation:

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Scissors

The question is about sql-server though – Gardas

despite the question being about SQL Server, this answer was very helpful for us using mysql who got to here by google! Google doesnt index the question tags, so thanks for this answer! – Consignor

Answer 7

You could refer to column alias but you need to define it using CROSS/OUTER APPLY:

SELECT s.logcount, s.logUserID, s.maxlogtm, c.daysdiff
FROM statslogsummary s
CROSS APPLY (SELECT DATEDIFF(day, s.maxlogtm, GETDATE()) AS daysdiff) c
WHERE c.daysdiff > 120;

DBFiddle Demo

Pros:

• single definition of expression(easier to maintain/no need of copying-paste)
• no need for wrapping entire query with CTE/outerquery
• possibility to refer in WHERE/GROUP BY/ORDER BY
• possible better performance(single execution)

Haldeman

its worth to mention that it only works in SQL Server – Macintosh

@MartinZinovsky Question is tagged with sql-server and t-sql :) – Haldeman

Answer 8

For me, the simplest way to use an ALIAS in the WHERE clause is to create a sub-query and select from it instead.

Example:

WITH Q1 AS (
    SELECT LENGTH(name) AS name_length,
    id,
    name
    FROM any_table
)

SELECT id, name, name_length FROM Q1 WHERE name_length > 0

Propylene

Answer 9

Came here looking something similar to that, but with a CASE WHEN, and ended using the where like this: WHERE (CASE WHEN COLUMN1=COLUMN2 THEN '1' ELSE '0' END) = 0 maybe you could use DATEDIFF in the WHERE directly. Something like:

SELECT logcount, logUserID, maxlogtm
FROM statslogsummary
WHERE (DATEDIFF(day, maxlogtm, GETDATE())) > 120

Intoxicant

Answer 10

Use the HAVING clause because the WHERE keyword cannot be used with aggregate functions.

SELECT
    logcount,
    logUserID,
    maxlogtm,
    DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
HAVING daysdiff > 120

Ammonia

Doesn't work on SQL Server though... – Diatropism