在Oracle中,是否需要使用'AS'关键字定义SELECT语句中列的别名?
我注意到
SELECT column_name AS "alias"
等同于
SELECT column_name "alias"
我想知道以后这种方式定义列别名会有什么后果。
AS
关键字(已测试)。 - mvorisek(在Oracle 11g
上测试过)
关于AS
:
AS
是可选的。AS
,否则会出错。关于双引号
:
例如
-- '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";
AS
吗?我很好奇这是为什么,因为从直觉上看这似乎相当不一致,而其他DBMS如MySQL则允许它。 - ZeroKnightSELECT employee_id,department_id AS department
FROM employees
order by department
SELECT employee_id,department_id AS "department"
FROM employees
order by department
--Oracle错误--
如果您在使用ORDER BY子句时,请尽可能不要使用AS和双引号。
department_id AS“Department Name”
,则需要执行ORDER BY“Department Name”
。 - fotijr两种都是正确的。Oracle允许使用两种方式。
我的结论是(在12c上进行测试):
因此:
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
SELECT employee_id,department_id AS "Department ID"
FROM employees
order by department
AS
只是更明确地提到别名的一种方式,这很好,因为一些依赖于这个小关键字的库。例如,JDBC 4.0。根据使用情况,可能会观察到不同的行为。