Re: JOIN on set of rows?

Peter Fein <pfein@pobox.com> writes:
> Sorry, I kinda wrote that wrong. ;) What I really want is:

> SELECT rows of known, app-generated (app_name, app_id)
> INTERSECT
> SELECT t1.symbol AS app_name, t2.outside_key AS app_id
> FROM t2 LEFT JOIN t1 ON t1.t2_id=t2.id

> There are around a max of 50 rows in the first select and
> perhaps up to 1 million in the second.

You could do it like

      (
    SELECT appname1, appid1
    UNION ALL
    SELECT appname2, appid2
    UNION ALL
    ...
    SELECT appnameN, appidN
      )
    INTERSECT
    SELECT ...

The UNION ALL bit is a tad grotty, but it will do until someone gets
around to implementing the full SQL VALUES construct.

            regards, tom lane