Re: Using the query INTERSECTion

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Vincenzo Romano <vincenzo(dot)romano(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Using the query INTERSECTion
Date: 2007-06-18 18:00:58
Message-ID: 21133.1182189658@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Vincenzo Romano <vincenzo(dot)romano(at)gmail(dot)com> writes:
> But now I have one more thing. The following command will fail with
> a syntax error:

> SELECT * FROM (SELECT 1 ) a INTERSECT (SELECT 2 ) b;

> Because of the second (harmless) table alias.
> In my mind it should work. Or not?

Not. INTERSECT is not like JOIN from a syntactic perspective.
According to the SQL spec, "something INTERSECT something" is
a <query expression>, and the only way to put one of those into
a FROM-list is to wrap it with parens (making it a <subquery>)
and then put an alias after it. This is because a FROM-list
is a list of <table reference>s, which have the syntax

<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>

<derived table> ::= <table subquery>

This works:
SELECT * FROM ((SELECT 1 ) INTERSECT (SELECT 2 )) a;

Aliases on the INTERSECT inputs don't work (and wouldn't have any
real use if they did). Your original example is actually getting
parsed as

(SELECT * FROM (SELECT 1 ) a) INTERSECT (SELECT 2 ) b;

which is OK, if redundant, up to the extraneous "b".

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Brian Hurt 2007-06-18 18:01:55 Re: [GENERAL] [PERFORM] [ADMIN] Postgres VS Oracle
Previous Message Andreas Kostyrka 2007-06-18 17:59:30 Re: [pgsql-advocacy] [PERFORM] Postgres VS Oracle