Обсуждение: Using the query INTERSECTion

Hello everyone.

In order to build some dynamic queries (EXECUTE under PL/PgSQL)
I'm taking in consideration to use the INTERSECT operator in order
to split a WHERE-condition in a static one and a dynamic one to be
built at runtime.

Instead of

SELECT * FROM joinedtables WHERE static_cond AND dynamic_cond;

I could use:

SELECT * FROM joinedtables WHERE static_cond
  INTERSECT
SELECT * FROM joinedtables WHERE dynamic_cond

I'm wondering what'd be the difference in efficiency between these
two queries.

Is there any advise?

Many thanks in advance.

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

On Mon, Jun 18, 2007 at 04:10:41PM +0200, Vincenzo Romano wrote:
> Hello everyone.
>
> In order to build some dynamic queries (EXECUTE under PL/PgSQL)
> I'm taking in consideration to use the INTERSECT operator in order
> to split a WHERE-condition in a static one and a dynamic one to be
> built at runtime.

The INTERSECT will almost certainly be slower, basically because all
the joins will have to be processed twice. Also, the results won't be
quite the same, especially with respect to duplicate records and NULLs.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Monday 18 June 2007 19:27:35 Martijn van Oosterhout wrote:
> On Mon, Jun 18, 2007 at 04:10:41PM +0200, Vincenzo Romano wrote:
> > Hello everyone.
> >
> > In order to build some dynamic queries (EXECUTE under PL/PgSQL)
> > I'm taking in consideration to use the INTERSECT operator in
> > order to split a WHERE-condition in a static one and a dynamic
> > one to be built at runtime.
>
> The INTERSECT will almost certainly be slower, basically because
> all the joins will have to be processed twice. Also, the results
> won't be quite the same, especially with respect to duplicate
> records and NULLs.
>
> Have a nice day,

I think you are right, but I could rely on the cache to be affective
and thus relieving the performance loss.

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?

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

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