SQL Oracle 中是否需要“as”关键字来定义别名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8451195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is the 'as' keyword required in Oracle to define an alias?
提问by Jonathan
Is the 'AS' keyword required in Oracle to define an alias name for a column in a SELECT statement?
Oracle 中是否需要“AS”关键字来为 SELECT 语句中的列定义别名？
I noticed that
我注意到
SELECT column_name AS "alias"
is the same as
是相同的
SELECT column_name "alias"
I am wondering what the consequences are of defining a column alias in the latter way.
我想知道以后一种方式定义列别名的后果是什么。
采纳答案by Roger Lindsj?
According to the select_list Oracle select documentationthe AS is optional.
根据 select_list Oracle select 文档，AS 是可选的。
As a personal note I think it is easier to read with the AS
作为个人笔记，我认为使用 AS 更容易阅读
回答by Eric Wang
(Tested on Oracle 11g)
（已测试Oracle 11g）
About AS:
关于AS：
- When used on result column,
ASis optional. - When used on table name,
ASshouldn't be added, otherwise it's an error.
- 在结果列上使用时，
AS是可选的。 - 用于table name 时，
AS不应添加，否则会出错。
About double quote:
关于double quote：
- It's optional & valid for both result column & table name.
- 它对结果列和表名都是可选的和有效的。
e.g
例如
-- 'AS' is optional for result column
select (1+1) as result from dual;
select (1+1) result from dual;
-- 'AS' shouldn't be used for table name
select 'hi' from dual d;
-- Adding double quotes for alias name is optional, but valid for both result column & table name,
select (1+1) as "result" from dual;
select (1+1) "result" from dual;
select 'hi' from dual "d";
回答by TNK
AS without double quotations is good.
没有双引号的 AS 很好。
SELECT employee_id,department_id AS department
FROM employees
order by department
--ok--
- 好的 -
SELECT employee_id,department_id AS "department"
FROM employees
order by department
--error on oracle--
--oracle 出错--
so better to use AS without double quotation if you use ORDER BY clause
如果使用 ORDER BY 子句，最好使用没有双引号的 AS
回答by Pawan Tejwani
Both are correct. Oracle allows the use of both.
两者都是正确的。Oracle 允许同时使用两者。
回答by Kishore Kumar
<kdb></kdb>is required when we have a space in Alias Name like
<kdb></kdb>当我们在别名中有一个空格时是必需的
SELECT employee_id,department_id AS "Department ID"
FROM employees
order by department
回答by iceSea
My conclusion is that（Tested on 12c）:
我的结论是（在12c上测试）：
- AS is always optional, either with or without ""; AS makes no difference(column alias only, you can not use AS preceding table alias)
- However, with or without "" does make difference because "" lets lower case possible for an alias
- AS 始终是可选的，带或不带“”；AS 没有区别（仅列别名，不能使用 AS 前表别名）
- 但是，带或不带 "" 确实有所不同，因为"" 允许小写字母作为别名
thus :
因此 ：
SELECT {T / t} FROM (SELECT 1 AS T FROM DUAL); -- Correct
SELECT "tEST" FROM (SELECT 1 AS "tEST" FROM DUAL); -- Correct
SELECT {"TEST" / tEST} FROM (SELECT 1 AS "tEST" FROM DUAL ); -- Incorrect
SELECT test_value AS "doggy" FROM test ORDER BY "doggy"; --Correct
SELECT test_value AS "doggy" FROM test WHERE "doggy" IS NOT NULL; --You can not do this, column alias not supported in WHERE & HAVING
SELECT * FROM test "doggy" WHERE "doggy".test_value IS NOT NULL; -- Do not use AS preceding table alias
So, the reason why USING AS AND "" causes problem is NOT AS
所以，USING AS AND "" 导致问题的原因不是 AS
Note:"" double quotes are required if alias contains space OR if it contains lower-case characters and MUST show-up in Result set as lower-case chars. In all other scenarios its OPTIONAL and can be ignored.
注意：如果别名包含空格或如果它包含小写字符并且必须在结果集中显示为小写字符，则需要 "" 双引号。在所有其他情况下，它是可选的，可以忽略。
回答by ray
There is no difference between both, ASis just a more explicit way of mentioning the alias which is good because some dependent librariesdepends on this small keyword. e.g. JDBC 4.0. Depend on use of it, different behaviour can be observed.
两者之间没有区别，AS只是一种更明确的提及别名的方式，这很好，因为某些依赖库依赖于这个小关键字。例如JDBC 4.0。根据它的使用情况，可以观察到不同的行为。
See this. I would always suggest to use the full form of semantic to avoid such issues.
看到这个。我总是建议使用完整形式的语义来避免此类问题。
