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

User · Answer

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

User · Answer

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  amp  valid for both result column  amp  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  amp  table name  select  1 1  as  result  from dual  select  1 1   result  from dual   select  hi  from dual  d

User · Answer

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

User · Answer

Both are correct  Oracle allows the use of both

User · Answer

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  amp  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

User · Answer

lt kdb gt  lt  kdb gt  is required when we have a space in Alias Name like  SELECT employee id department id AS  Department ID  FROM employees order by department

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to alias

Examples related to required