473,216 Members | 1,311 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

AS operator and subselect result names: PostgreSQL vs. Oracle

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
3 7166
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

125
by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database (and some are bloated such as Oracle) from...
7
by: Alex | last post by:
Hi, I have some problems with creating a query that will replace values in one table from another one. Table 1: userName : refCode1 : refCode2 ------------------------------ alex : 12 ...
6
by: Greg Stark | last post by:
So I have a query in which some of the select values are subqueries. The subqueries are aggregates so I don't want to turn this into a join, it would become too complex and postgres would have...
2
by: Chris | last post by:
I think I already know that the answer is that this can't be done, but I'll ask anyways. Suppose you want to use an RDBMS to store messages for a threaded message forum like usenet and then...
4
by: James | last post by:
I have a performance problem with the following query and variations on the subselect. The EXISTS version of the first example will complete in ~10 minutes. The NOT logic in both the examples...
4
by: dtwalter | last post by:
Is it possible to ORDER BY a SubSelect? I don't see why it wouldn't be, but I'm having some trouble. Hopefully it's just a simple error in syntax and somebody can tell me. Here's what I'm trying...
3
by: Jim C. Nasby | last post by:
I'm sure this has been answered before, but the search seems to be down again. How can I convert the results of a subselect into an array? IE: CREATE TABLE a(a int, b int, c int); INSERT INTO...
6
by: Alex P | last post by:
Hi, when creating a query with a subselect SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) AS max_pop FROM states; then it is not possible to sort after max_pop...
2
by: Morten K. Poulsen | last post by:
(re-post) Dear list, Please let me know if this is not the list to ask this kind of question. I am trying to optimize a query that joins two relatively large (750000 rows in each) tables....
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.