SQL> select sal,comm from emp
2 where sal<2000;
SAL COMM
---------- ----------
800
1600 300
1250 500
1250 1400
1500 0
1100
950
1300
8 rows selected.
SQL> select sal as salary,comm as commission from emp
2 where salary<2000;
where salary<2000
*
ERROR at line 2:
ORA-00904: "SALARY": invalid identifier
SQL> select * from
2 (
3 select sal as salary,comm as commission from emp
4 ) x
5 where salary<2000;
SALARY COMMISSION
---------- ----------
800
1600 300
1250 500
1250 1400
1500 0
1100
950
1300
8 rows selected.
将取别名的查询作为内联视图,便可以在外部查询中引用其中的别名列。为什么要这么做呢?WHERE子句是在SELECT之前进行处理的,这样,在处理求解“问题”查询的WHERE子句之前,SALARY和COMMISSION并不存在,要到WHERE子句处理完成之后,别名才生效。然而,FROM子句是在WHERE之前处理的。将原查询放在FROM子句中,那么,在最外层的WHERE子句之前,以及最外层的WHERE子句“看到”别名之前,就已经生成了查询结果。