1

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?

Improve this question
0

5 Answers 5

Reset to default
4

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

select * from ( SELECT salary/12 AS sal FROM sdatabase ) dt WHERE sal > 1000
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I guess it comes down to personal preference, but this is much less readable to me than just using salary/12 in a where clause.
@HoneyboyWilson, well salary/12 isn't perhaps the best example. But when it comes to more complex expressions, a derived table like this makes the code easier to write, and less risky to update!
i agree with jarth this way is best for readablity +1 for that.. @HoneyboyWilson but still advice you to write SELECT salary/12 AS sal FROM t WHERE salary/12 > 1000 as i believe most SQL optimizers would see that jarth's query condition WHERE sal > 1000 can be pushed down into the inner select annyway .. MySQL 8.0 optimizer for example rewites it into select (test.sdatabase.salary / 12) AS sal from test.sdatabase where ((test.sdatabase.salary / 12) > 1000) <- notice the derived table (subquery) is gone..
1

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.

Improve this answer

2 Comments

Are you aware of any resources that explain why the language was originally designed to evaluate the clauses in a different order to which they are written?
You can read this article written by Itzik Ben-Gan -> itprotoday.com/sql-server/… He writes about sql server, but the principles are the same.
0

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
Improve this answer

Comments

0

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
Improve this answer

Comments

0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.