Re: Using the query INTERSECTion

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