Vincenzo Romano <vincenzo.romano@xxxxxxxxx> 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
