Oracle AS keyword and subqueries

Question

Just found out that Oracle does not like it when you use the AS keyword to alias a subquery:

SELECT * FROM (SELECT * FROM products) AS p

I need to keep my SQL queries as portable as possible. Will the removal of the AS keyword in the above query affect any other RDBMS?

Wisteria

Answer 1

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)

Apuleius

I know MySQL requires an alias, my question was about the usage of the AS keyword. – Wisteria

Thanks. A link, or a quote, from the standard you are referring to would be very valuable. – Wisteria

It wouldn't mean anything in terms of RDBMS compliance though – Morez

@MaxToro I put a link to a PDF document. But as DavidAldridge said, 'it wouldn't mean anything in terms of RDBMS' – Apuleius

@MaxToro I know that has been some time since this question, did it solve your problem? If yes, please consider in accepting it :) – Apuleius