Oracle AS keyword and subqueries

问题:

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?

回答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)