Re: using INTERSECT and UNION in IN clause

On Tue, Aug 22, 2000 at 01:50:26PM +0400, Alex Guryanow wrote:
> Hi,
>
> postgresql 7.0.2. Why by executing the following query
>
> select * from magazine
> where id in (
>       select mag_id from dict where word = 'akademie' intersect
>       select mag_id from dict where word = 'der' intersect
>       select mag_id from dict where word = 'klasse' )
>
> I receive the following error:
>
> ERROR: parse error at or near 'intersect'

[snip]

>
> Is it possible to use INTERSECT and UNION keywords in subqueries?

I guess not. I imagine this limitation will be lifted in a future version.

An alternative possibility is joining the table to itself on mag_id:

  select * from magazine
  where id in (
        select d1.mag_id from dict as d1, dict as d2, dict as d3
        where d1.word = 'akademie' and d2.word='der'
        and d3.word='klasse'
        and d1.mag_id = d2.mag_id and d2.mag_id = d3.mag_id)

In fact, do the whole thing as one big join:

  select * from magazine,dict as d1, dict as d2, dict as d3
        where d1.word = 'akademie' and d2.word='der'
        and d3.word='klasse'
        and d1.mag_id = d2.mag_id and d2.mag_id = d3.mag_id
    and magazine.id = d1.mag_id;

This should work optimally if you have indexes on

magazine(id)
dict(mag_id)
dict(word)

Hope that helps,

Jules