Is the 'as' keyword required in Oracle to define an alias?

Question

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.

Note that double quotes for the alias-name are required (as opposed to single quotes). A small, but important, point. — JosephDoggie, Jan 19 '22 at 14:55

score 52 · Accepted Answer · answered Dec 09 '11 at 20:21

52

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

answered Dec 09 '11 at 20:21

Roger Lindsjö

11,330
1
42
53

8

in joined table alias, `AS` is not supported (tested on Oracle 11c) – mvorisek Sep 13 '20 at 22:13

score 35 · Answer 2 · answered Dec 05 '16 at 07:28

35

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

answered Dec 05 '16 at 07:28

Eric

22,183
20
145
196

Do you know of any reason why Oracle disallows `AS` with a table name? I'm curious as to why this is, as it seems rather inconsistent intuition-wise, and other DBMS such as MySQL allow it. – ZeroKnight May 13 '20 at 20:37
@ZeroKnight No idea, maybe due to history reason. – Eric May 14 '20 at 00:41

score 9 · Answer 3 · answered Jan 08 '14 at 16:13

9

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

answered Jan 08 '14 at 16:13

TNK

195
1
2
7

1

You can use an ORDER BY with a double quotation alias. This is especially helpfully if you have a space in the column name. So if your alias was `department_id AS "Department Name"` you would need to do `ORDER BY "Department Name"`. – fotijr Aug 27 '14 at 16:20

score 7 · Answer 4 · edited Oct 17 '18 at 08:32

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.

score 7 · Answer 5 · answered Dec 09 '11 at 20:26

7

Both are correct. Oracle allows the use of both.

answered Dec 09 '11 at 20:26

Pawan Tejwani

109
1
1
6

score 5 · Answer 6 · edited Oct 31 '21 at 09:46

5

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

edited Oct 31 '21 at 09:46

ΩmegaMan

29,542
12
100
122

answered Jan 05 '15 at 15:35

Kishore Kumar

12,675
27
97
154

1

This should have been a comment – Jonathan Sep 10 '16 at 18:33

score 1 · Answer 7 · answered Feb 15 '19 at 05:52

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.

Is the 'as' keyword required in Oracle to define an alias?

7 Answers7

AS without double quotations is good.

--ok--

Linked