How to reference an aliased column in the WHERE clause in SQL?

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

You have used aliases to provide more meaningful column names for your result set
and would like to exclude some of the rows using the WHERE clause. However, your
attempt to reference alias names in the WHERE clause fails.

SELECT sal as salary, commission as commission
FROM Staff WHERE salary < 5000

Output

ERROR 1054 (42S22) : Unknown column 'salary' in 'where clause'

By wrapping your query as an inline view, you can reference the aliased columns:

SELECT *
FROM (
SELECT sal as salary, commission
FROM Staff ) x
WHERE salary < 5000

Output

salary comission
800
1600 300
1250 500
2975
1250 1400
2850
2450
3000
1500 0
1100
950
3000
1300

Discussion

In this simple example, you can avoid the inline view and reference SAL
directly in the WHERE clause to achieve the same result. This solution introduces
you to what you would need to do when attempting to reference any of the following
in a WHERE clause:

  • Aggregate functions
  • Scalar subqueries
  • Windowing functions
  • Aliases

Placing your query, the one giving aliases, in an inline view gives you the ability to
reference the aliased columns in your outer query. Why do you need to do this? The
WHERE clause is evaluated before the SELECT; thus, SALARY does not yet exist when the Problem query’s WHERE clause is evaluated. Those aliases are not applied until after the WHERE clause processing is complete. However, the FROM clause is evaluated before the WHERE. By placing the original query in a FROM clause, the results from that query are generated before the outermost WHERE clause, and your outermost WHERE clause sees the alias names. This technique is particularly useful when the columns in a table are not named particularly
well.