Thread: Using intersect in subquery
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
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, Pennsylvania19026
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
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
> >> (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, Pennsylvania19026
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 > >