SQL for absolute value greater than some value

Question

The following SQL works:

select
  column1
from
  table1
where
  column1 > 10

The following doesn't:

select
  abs(column1 - column2) column_diff
from
  table1
where
  column_diff > 10

Why? How should I fix it? All I want to say is that 'give me all the rows with the absolute value of the difference of the two columns greater than 10'. Thanks.

Use a subquery, CTE, or put the expression in the `where` clause. That is how SQL works. — Gordon Linoff, Sep 19 '17 at 18:31
Possible duplicate of [Referring to a Column Alias in a WHERE Clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) — Tab Alleman, Sep 19 '17 at 18:35

score 1 · Accepted Answer · answered Sep 19 '17 at 18:39

Your query won't work because sql engine will compile your query in following steps:

from clause
where clause
select clause

So, while executing where clause, alias column_diff won't be available hence the query won't work.

Solution:

select 
     abs(column1 - column2) as column_diff
         from
           table1
               where
                 abs(column1-column2) >10;

Hope it helps!

SQL for absolute value greater than some value

1 Answers1