Is the 'As' Keyword Required in Oracle to Define an Alias

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

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

How to use the 'as' keyword to alias a table in Oracle?

You can use AS for table aliasing on many SQL servers (at least MsSQL, MySQL, PostrgreSQL) but it's always optional and on Oracle it's illegal.

So remove the AS :

SELECT G.Guest_ID, G.First_Name, G.Last_Name
FROM Guest G

Oracle column alias in select: is AS mandatory?

A view is just a stored query, so the select syntax applies. As you can see from the syntax diagram for the select list items:

... the AS keyword in the expr AS c_alias section is optional. So no, it makes no difference to the query, database or view. It's for readability and consistency with other database systems. I prefer to use it for anything other than a quick ad hoc query, and you may have coding standards that require it, but Oracle does not care.

oracle: can you assign an alias to the from clause?

Yes, Oracle supports table aliases. It supports AS in the SELECT list but not in the FROM list:

SELECT a.col - b.col AS markup
  FROM RETAIL a,
       COST b
 WHERE b.id = a.id

Most databases support omitting the AS keyword.

That said, table aliases aren't column aliases -- you still need to reference a specific column in the respective table in the SELECT clause, like you see in my update of your example. I also added the WHERE criteria so the query wouldn't be returning a Cartesian product.

Table aliases are sometimes required for derived tables/inline views (AKA subquery, though I find the terminology very vague):

SELECT x.col
  FROM (SELECT t.col,
               MAX(t.date)
          FROM TABLE t
      GROUP BY t.col) x

Here's your query:

Your problem was you were putting the table alias inside the derived table, when it needs to be outside the brackets/parenthesis:

SELECT DISTINCT TO_CHAR(MONTHS_BETWEEN(x.pubdate, y.pubdate), '99.99') AS "Answer"
 FROM (SELECT DISTINCT a.pubdate FROM BOOKS3 a WHERE a.pubid = 2) x,
      (SELECT DISTINCT b.pubdate FROM BOOKS3 b WHERE b.pubid = 4) y

The reason you need the distinct is because of the Cartesian product.

What's the purpose of SQL keyword AS ?

There is no difference between both statements above. AS is just a more explicit way of mentioning the alias

What is the point using AS keyword in SQL when aliasing can be done without it?

I think the reason is simple. Consider code such as the following:

select a, b, c, d
. . .

It is very easy to occasionally skip the comma:

select a b, c, d

If you don't use as then this looks like correct code and it can be difficult to figure out. If you always use as for column aliases, then you know it is incorrect.

Oracle column Alias - from keyword not found where expected

Use double quotes:

SELECT col1 "COLUMN 1" FROM TABLENAME

Oracle AS keyword and subqueries

The pattern for the SQL 99 ANSI is that the table can have an alias WITHOUT the AS keyword so, you can take out AS and it should work on every RDBMS. See it on fiddle:

• MySQL
• Oracle
• PostgreSql
• SQLLite
• SQLServer

In ISO/IEC 9075-2:1999, section 7.6 <table reference>, page 232:

<table reference> ::=
   <table primary>
   | <joined table>

<table primary> ::=
   <table or query name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
   | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <lateral derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <collection derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <only spec>
      [ [ AS ] <correlation name>
         [ <left paren> <derived column list> <right paren> ] ]
   | <left paren> <joined table> <right paren>

---

Also confirmed to work:

• MS Access (Jet)

---

Related Topics