By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,455 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

AS operator and subselect result names: PostgreSQL vs. Oracle

P: n/a
Hello,

I would like to ask the about the following...

PostgreSQL allows tables resulting from subselects to be renamed with
an optional AS keyword whereas Oracle 9 will report an error whenever
a table is renamed with the AS keyword. Furthermore, in PostgreSQL
when the result of a subselect is referenced in an outer select
it is required that the subselect result be named, whereas this
is not true in Oracle. I wonder what standard SQL has to say
about these two issues. In particular:

1. Does standard SQL allow an optional AS keyword for (re/)naming
tables including those resulting from subselects.

and

2 Why must a subselect whose fields are referenced in an outer query
be explicitly named in PostgreSQL when it is not necessary in Oracle.

Thanks,

Neil
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Neil Zanella writes:
1. Does standard SQL allow an optional AS keyword for (re/)naming
tables including those resulting from subselects.
Yes.
2 Why must a subselect whose fields are referenced in an outer query
be explicitly named in PostgreSQL when it is not necessary in Oracle.


Because the SQL standard says so.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

P: n/a
On Fri, 31 Oct 2003, Neil Zanella wrote:
Hello,

I would like to ask the about the following...

PostgreSQL allows tables resulting from subselects to be renamed with
an optional AS keyword whereas Oracle 9 will report an error whenever
a table is renamed with the AS keyword. Furthermore, in PostgreSQL
when the result of a subselect is referenced in an outer select
it is required that the subselect result be named, whereas this
is not true in Oracle. I wonder what standard SQL has to say
about these two issues. In particular:

1. Does standard SQL allow an optional AS keyword for (re/)naming
tables including those resulting from subselects.

and

2 Why must a subselect whose fields are referenced in an outer query
be explicitly named in PostgreSQL when it is not necessary in Oracle.


I believe the section in question of SQL92 that you're asking about
says explicitly that a table reference from a derived table should look
like:
<derived table> [ AS ] <correlation name> [ <left paren> <derived column
list> <right paren> ]
where <derived table> is a table subquery.

It's possible that SQL99 changes this, but in SQL92 at least, it looks
like the correlation name is not optional (although the AS keyword is).
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3

P: n/a
nz******@cs.mun.ca (Neil Zanella) writes:
PostgreSQL allows tables resulting from subselects to be renamed with
an optional AS keyword whereas Oracle 9 will report an error whenever
a table is renamed with the AS keyword. Furthermore, in PostgreSQL
when the result of a subselect is referenced in an outer select
it is required that the subselect result be named, whereas this
is not true in Oracle. I wonder what standard SQL has to say
about these two issues.
The standard agrees with us.

SQL99 section 7.5 <from clause> says that FROM clause items are
<table reference>s:

<from clause> ::=
FROM <table reference list>

<table reference list> ::=
<table reference> [ { <comma> <table reference> }... ]

the syntax for which appears in 7.6 <table reference>:

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

<derived table> ::= <table subquery>

[ I've omitted the definitions for other cases ]

and in 7.14 we find

<table subquery> ::= <subquery>

<subquery> ::=
<left paren> <query expression> <right paren>

So the second alternative (<derived table> ...) is the one that allows a
sub-select.

Notice that the AS-clause ([ AS ] <correlation name> [ <left paren>
<derived column list> <right paren> ]) is bracketed as a whole, making
it optional, in just two of the five alternatives where it appears.
It is required by the syntax in the <derived table> case.

1. Does standard SQL allow an optional AS keyword for (re/)naming
tables including those resulting from subselects.
It does not "allow" it, it requires it.
2 Why must a subselect whose fields are referenced in an outer query
be explicitly named in PostgreSQL when it is not necessary in Oracle.


We insist on a name because otherwise we'd have to invent a name for the
FROM-clause item, and in most cases there's not an obvious choice for a
default name. I dunno what Oracle does about choosing a name, but it's
not standard behavior.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.