CODEWITHSUNDEEP
sqlalias
beginsql
beginsql

Reputation: 135

How to use aliases in WHERE clause?

Simple question: How to use aliases after SELECT statement? Let's say i have query like this: 

SELECT salary/12 AS sal 
FROM sdatabase
WHERE sal > 1000

Of course it won't work because database will throw me an error. I know I can just replace sal with salary/12 like this: 

WHERE salary/12 > 1000

but I think it is less readable. So is there anything I can do with it or this is just the way it's done?

Upvotes: 1

Views: 851

Answers (5)

mkRabbani
mkRabbani

Reputation: 16908

If you are learning How to use Alias in WHERE statement, Then @Jarlh's solution is fine but in practical, using the filter condition directly as below make more sense. This exclude use of a sub-query with the same result.

SELECT salary/12 AS sal 
FROM sdatabase
WHERE salary > 12000

Upvotes: 0

Shantanu Sharma
Shantanu Sharma

Reputation: 4099

You can try Common Table Expression (CTE) -

WITH cte AS
(SELECT salary/12 AS sal 
FROM sdatabase
WHERE sal > 1000)

SELECT * 
FROM cte
WHERE sal > 1000

Upvotes: 0

M. Kanarkowski
M. Kanarkowski

Reputation: 2205

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:

  1. FROM
  2. JOIN
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. SELECT
  7. DISTINCT
  8. ORDER BY

You can find more information about it by typing logical query processing in your browser.

Upvotes: 1

Vladimir Galindo
Vladimir Galindo

Reputation: 1

From a chronological stand point your alias is not available in the "WHERE", so you need a subQuery to achieve this...

SELECT * FROM 
( SELECT salary/12 AS sal FROM sdatabase ) 
sq WHERE sal > 1000

Upvotes: 0

jarlh
jarlh

Reputation: 44795

Wrap the original query up as a derived table (subquery):

select *
from
(
    SELECT salary/12 AS sal
    FROM sdatabase
) dt
WHERE sal > 1000

Upvotes: 4

Related Questions