Is the 'as' keyword required in Oracle to

问题:

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.

回答1:

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

回答2:

(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";

回答3:

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

回答4:

Both are correct. Oracle allows the use of both.

回答5:

<kdb></kdb> is required when we have a space in Alias Name like

SELECT employee_id,department_id AS "Department ID"
FROM employees
order by department

回答6:

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.