pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing intersect in subquery
7+ messages / 4 participants
[nested] [flat]
* Using intersect in subquery
@ 1999-08-18 00:48 Hamid Khoshnevis <hamid@emarq.com>
0 siblings, 0 replies; 7+ messages in thread
From: Hamid Khoshnevis @ 1999-08-18 00:48 UTC (permalink / raw)
To: pgsql-sql
I have tried to use intersect in subquery causing the following error.
Please help!
ERROR: parser: parse error at or near "intersect"
select * from Table1
where KeyField1 in
(select KeyField2 from Table2 where somecondition
intersect
select KeyField3 from Table3 where somecondition);
(I believe) I am running PG 6.4. The INTERSECT by itself works, but not in
a subquery.
Thanks!
hamid
^ permalink raw reply [nested|flat] 7+ messages in thread
* Using intersect in subquery
@ 1999-08-18 01:55 Hamid Khoshnevis <hamid@emarq.com>
1999-08-18 02:38 ` Re: [SQL] Using intersect in subquery Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Hamid Khoshnevis @ 1999-08-18 01:55 UTC (permalink / raw)
To: pgsql-sql
I have tried to use intersect in subquery causing the following error.
Please help!
ERROR: parser: parse error at or near "intersect"
select * from Table1
where KeyField1 in
(select KeyField2 from Table2 where somecondition
intersect
select KeyField3 from Table3 where somecondition);
(I believe) I am running PG 6.4. The INTERSECT by itself works, but not in
a subquery.
Thanks!
hamid
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Using intersect in subquery
1999-08-18 01:55 Using intersect in subquery Hamid Khoshnevis <hamid@emarq.com>
@ 1999-08-18 02:38 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-08-18 13:51 ` Re: [SQL] Using intersect in subquery Tom Lane <tgl@sss.pgh.pa.us>
1999-08-18 14:21 ` Re: [SQL] Using intersect in subquery Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 2 replies; 7+ messages in thread
From: Bruce Momjian @ 1999-08-18 02:38 UTC (permalink / raw)
To: Hamid Khoshnevis <hamid@emarq.com>; +Cc: pgsql-sql
Try 6.5. We fixed some Intersect stuff.
[Charset iso-8859-1 unsupported, filtering to ASCII...]
>
> I have tried to use intersect in subquery causing the following error.
> Please help!
> ERROR: parser: parse error at or near "intersect"
>
>
> select * from Table1
> where KeyField1 in
> (select KeyField2 from Table2 where somecondition
> intersect
> select KeyField3 from Table3 where somecondition);
>
> (I believe) I am running PG 6.4. The INTERSECT by itself works, but not in
> a subquery.
>
> Thanks!
>
> hamid
>
>
>
>
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Using intersect in subquery
1999-08-18 01:55 Using intersect in subquery Hamid Khoshnevis <hamid@emarq.com>
1999-08-18 02:38 ` Re: [SQL] Using intersect in subquery Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-08-18 13:51 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-18 15:10 ` Re: [SQL] Using intersect in subquery Bruce Momjian <maillist@candle.pha.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 1999-08-18 13:51 UTC (permalink / raw)
To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: Hamid Khoshnevis <hamid@emarq.com>; pgsql-sql
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Try 6.5. We fixed some Intersect stuff.
>>
>> select * from Table1
>> where KeyField1 in
>> (select KeyField2 from Table2 where somecondition
>> intersect
>> select KeyField3 from Table3 where somecondition);
>>
>> (I believe) I am running PG 6.4. The INTERSECT by itself works, but not in
>> a subquery.
>>
No, it still won't work --- the current grammar specifies SubSelect not
select_clause as the kind of select you can put inside an expression.
Not sure what it would take to fix this; I have a feeling that just
changing the grammar wouldn't be good enough :-(. Better add it to the
TODO list:
* Support UNION/INTERSECT/EXCEPT in sub-selects
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Using intersect in subquery
1999-08-18 01:55 Using intersect in subquery Hamid Khoshnevis <hamid@emarq.com>
1999-08-18 02:38 ` Re: [SQL] Using intersect in subquery Bruce Momjian <maillist@candle.pha.pa.us>
1999-08-18 13:51 ` Re: [SQL] Using intersect in subquery Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-08-18 15:10 ` Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 1999-08-18 15:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hamid Khoshnevis <hamid@emarq.com>; pgsql-sql
> >> (I believe) I am running PG 6.4. The INTERSECT by itself works, but not in
> >> a subquery.
> >>
>
> No, it still won't work --- the current grammar specifies SubSelect not
> select_clause as the kind of select you can put inside an expression.
> Not sure what it would take to fix this; I have a feeling that just
> changing the grammar wouldn't be good enough :-(. Better add it to the
> TODO list:
> * Support UNION/INTERSECT/EXCEPT in sub-selects
Done.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Using intersect in subquery
1999-08-18 01:55 Using intersect in subquery Hamid Khoshnevis <hamid@emarq.com>
1999-08-18 02:38 ` Re: [SQL] Using intersect in subquery Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-08-18 14:21 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-19 21:28 ` RE: [SQL] Using intersect in subquery Hamid Khoshnevis <hamid@emarq.com>
1 sibling, 1 reply; 7+ messages in thread
From: Herouth Maoz @ 1999-08-18 14:21 UTC (permalink / raw)
To: Hamid Khoshnevis <hamid@emarq.com>; pgsql-sql
At 16:51 +0300 on 18/08/1999, Tom Lane wrote:
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Try 6.5. We fixed some Intersect stuff.
> >>
> >> select * from Table1
> >> where KeyField1 in
> >> (select KeyField2 from Table2 where somecondition
> >> intersect
> >> select KeyField3 from Table3 where somecondition);
> >>
> >> (I believe) I am running PG 6.4. The INTERSECT by itself works, but
>not in
> >> a subquery.
> >>
>
> No, it still won't work --- the current grammar specifies SubSelect not
> select_clause as the kind of select you can put inside an expression.
> Not sure what it would take to fix this; I have a feeling that just
> changing the grammar wouldn't be good enough :-(. Better add it to the
> TODO list:
> * Support UNION/INTERSECT/EXCEPT in sub-selects
In the meantime I suppose changing the query might work. Intersect should
return only the rows which are returned from both tables, but since we are
talking about only one field here, it may be convenient to just do an
internal join?
SELECT * FROM Table1
WHERE KeyField in
( SELECT Keyfield2
FROM Table2, Table3
WHERE Keyfield2 = Keyfield3
);
This is not equivalent in the case of a NULL, but in all other cases, I
believe it is.
Or maybe the following is more efficient (and clear?):
SELECT * FROM Table1
WHERE EXISTS
( SELECT *
FROM Table2
WHERE KeyField2 = KeyField
)
AND EXISTS
( SELECT *
FROM Table3
WHERE KeyField3 = KeyField
);
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [SQL] Using intersect in subquery
1999-08-18 01:55 Using intersect in subquery Hamid Khoshnevis <hamid@emarq.com>
1999-08-18 02:38 ` Re: [SQL] Using intersect in subquery Bruce Momjian <maillist@candle.pha.pa.us>
1999-08-18 14:21 ` Re: [SQL] Using intersect in subquery Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-08-19 21:28 ` Hamid Khoshnevis <hamid@emarq.com>
0 siblings, 0 replies; 7+ messages in thread
From: Hamid Khoshnevis @ 1999-08-19 21:28 UTC (permalink / raw)
To: pgsql-sql
I re-implemented the intersect using sub-selects. It worked fine. Then I
tried Herouth's solution of:
SELECT * FROM Table1
WHERE KeyField in
( SELECT Keyfield2
FROM Table2, Table3
WHERE Keyfield2 = Keyfield3
);
This one is blazingly fast (by a factor of 4) compared to sub-select. Moral
of the story IMHO: Use flat select with a lot of joins vs. intersects or
sub-selects to get fast response (anyone?).
hamid
> -----Original Message-----
> From: Herouth Maoz [mailto:herouth@oumail.openu.ac.il]
> Sent: Wednesday, August 18, 1999 7:22 AM
> To: Hamid Khoshnevis; pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] Using intersect in subquery
>
>
> At 16:51 +0300 on 18/08/1999, Tom Lane wrote:
>
>
> > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > > Try 6.5. We fixed some Intersect stuff.
> > >>
> > >> select * from Table1
> > >> where KeyField1 in
> > >> (select KeyField2 from Table2 where somecondition
> > >> intersect
> > >> select KeyField3 from Table3 where somecondition);
> > >>
> > >> (I believe) I am running PG 6.4. The INTERSECT by itself works, but
> >not in
> > >> a subquery.
> > >>
> >
> > No, it still won't work --- the current grammar specifies SubSelect not
> > select_clause as the kind of select you can put inside an expression.
> > Not sure what it would take to fix this; I have a feeling that just
> > changing the grammar wouldn't be good enough :-(. Better add it to the
> > TODO list:
> > * Support UNION/INTERSECT/EXCEPT in sub-selects
>
> In the meantime I suppose changing the query might work. Intersect should
> return only the rows which are returned from both tables, but since we are
> talking about only one field here, it may be convenient to just do an
> internal join?
>
> SELECT * FROM Table1
> WHERE KeyField in
> ( SELECT Keyfield2
> FROM Table2, Table3
> WHERE Keyfield2 = Keyfield3
> );
>
> This is not equivalent in the case of a NULL, but in all other cases, I
> believe it is.
>
> Or maybe the following is more efficient (and clear?):
>
> SELECT * FROM Table1
> WHERE EXISTS
> ( SELECT *
> FROM Table2
> WHERE KeyField2 = KeyField
> )
> AND EXISTS
> ( SELECT *
> FROM Table3
> WHERE KeyField3 = KeyField
> );
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-08-19 21:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-18 00:48 Using intersect in subquery Hamid Khoshnevis <hamid@emarq.com>
1999-08-18 01:55 Using intersect in subquery Hamid Khoshnevis <hamid@emarq.com>
1999-08-18 02:38 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-08-18 13:51 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-18 15:10 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-08-18 14:21 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-19 21:28 ` Hamid Khoshnevis <hamid@emarq.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox