An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING
clauses to refer to the column.Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is
evaluated, the column value may not yet have been determined.
So, the following query is illegal:
SQL> SELECT empno AS employee, deptno AS department, sal AS salary
2 FROM emp
3 WHERE employee = 7369;
WHERE employee = 7369
*
ERROR at line 3:
ORA-00904: "EMPLOYEE": invalid identifier
SQL>
The column alias is allowed in:
- GROUP BY
- ORDER BY
- HAVING
You could refer to the column alias in WHERE clause in the following cases:
- Sub-query
- Common Table Expression(CTE)
For example,
SQL> SELECT * FROM
2 (
3 SELECT empno AS employee, deptno AS department, sal AS salary
4 FROM emp
5 )
6 WHERE employee = 7369;
EMPLOYEE DEPARTMENT SALARY
---------- ---------- ----------
7369 20 800
SQL> WITH DATA AS(
2 SELECT empno AS employee, deptno AS department, sal AS salary
3 FROM emp
4 )
5 SELECT * FROM DATA
6 WHERE employee = 7369;
EMPLOYEE DEPARTMENT SALARY
---------- ---------- ----------
7369 20 800
SQL>