pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Using 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