Is the 'as' keyword required in Oracle to define an alias?
SqlOracleAliasRequiredSql Problem Overview
Is the 'AS' keyword required in Oracle to define an alias name for a column in a SELECT statement?
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.
Sql Solutions
Solution 1 - Sql
According to the select_list Oracle select documentation the AS is optional.
As a personal note I think it is easier to read with the AS
Solution 2 - Sql
(Tested on Oracle 11g
)
About AS
:
- When used on result column,
AS
is optional. - When used on table name,
AS
shouldn't be added, otherwise it's an error.
About 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";
Solution 3 - Sql
AS without double quotations is good.
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--
so better to use AS without double quotation if you use ORDER BY clause
Solution 4 - Sql
Both are correct. Oracle allows the use of both.
Solution 5 - Sql
My conclusion is that(Tested on 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
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
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.
Solution 6 - Sql
The quotes are required when we have a space in Alias Name like
SELECT employee_id,department_id AS "Department ID"
FROM employees
order by department
Solution 7 - Sql
There is no difference between both, AS
is just a more explicit way of mentioning the alias which is good because some dependent libraries depends on this small keyword. e.g. JDBC 4.0. Depend on use of it, different behaviour can be observed.
See this. I would always suggest to use the full form of semantic to avoid such issues.