From: | Jules Bean <jules(at)jellybean(dot)co(dot)uk> |
---|---|
To: | Alex Guryanow <gav(at)nlr(dot)ru> |
Cc: | pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgres(dot)org |
Subject: | Re: using INTERSECT and UNION in IN clause |
Date: | 2000-08-22 12:03:25 |
Message-ID: | 20000822130324.H14698@grommit.office.vi.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Maxwell | 2000-08-22 14:01:30 | Stupid database use the index! |
Previous Message | Alex Guryanow | 2000-08-22 09:50:26 | using INTERSECT and UNION in IN clause |