Oracle SQL中子查询别名为何不能使用AS关键字？

Oracle SQL语法有明确规定：给子查询（内联视图）设置别名时，不允许使用AS关键字，但列别名、普通表的别名可以正常使用AS。这是Oracle和其他数据库（如MySQL、PostgreSQL）的语法差异点，也是你遇到"command not properly ended"错误的核心原因——Oracle解析器无法识别子查询别名前的AS，判定为无效语法。

报错代码（含AS的子查询别名） #

select ctable.code, company.founder, ctable.lman, ctable.sman, ctable.man, ctable.emp
from (select 
c.company_code as code, 
count(distinct l.lead_manager_code) as lman, 
count(distinct s.senior_manager_code) as sman,
count(distinct m.manager_code) as man,
count(distinct e.employee_code) as emp
from company c join lead_manager l on c.company_code = l.company_code
join senior_manager s on c.company_code = s.company_code
join manager m on c.company_code = m.company_code
join employee e on c.company_code = e.company_code 
group by c.company_code) AS ctable 
join company on company.company_code = ctable.code
order by ctable.code;

正常代码（移除AS的子查询别名） #

select ctable.code, company.founder, ctable.lman, ctable.sman, ctable.man, ctable.emp
from (select 
c.company_code as code, 
count(distinct l.lead_manager_code) as lman, 
count(distinct s.senior_manager_code) as sman,
count(distinct m.manager_code) as man,
count(distinct e.employee_code) as emp
from company c join lead_manager l on c.company_code = l.company_code
join senior_manager s on c.company_code = s.company_code
join manager m on c.company_code = m.company_code
join employee e on c.company_code = e.company_code 
group by c.company_code) ctable 
join company on company.company_code = ctable.code
order by ctable.code;

简单总结：Oracle里列别名可以用AS，但子查询/内联视图的别名必须直接跟在括号后，不能加AS，这是语法层面的硬性规定，记住这个差异就能避免这类低级错误。

内容的提问来源于stack exchange，提问作者jjurss