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 a WHERE clause, you can reference a variety of elements, including aggregate functions, scalar subqueries, and windowing functions. To reference an aliased column in your outer query, you can use an inline view, which is a subquery that has a name and can be referred to as a table. This is necessary because the WHERE clause is executed before the SELECT clause, so the aliased columns do not yet exist when the WHERE clause is being evaluated. The FROM clause, on the other hand, is considered before the WHERE clause, so the original query’s results are generated before the outermost WHERE clause is applied. This is especially helpful if a table’s columns don’t have appropriate titles.
Code Runner
Copy the below queries in the code runner to see the result
SELECT sal as salary, commission as commission
FROM Staff WHERE salary < 5000
SELECT *
FROM (
SELECT sal as salary, commission
FROM Staff ) x
WHERE salary < 5000