Postgres Pro
Downloads
Home   >   mailing lists

Thread: Rewrite without correlated subqueries

Rewrite without correlated subqueries

From
bricklen
Date:
Hi All,<br /><br />I'm having some trouble wrapping my head around the syntax to rewrite a query using correlated
subqueries,to using outer joins etc.<br /><br />The query:<br /><br />SELECT  ps.userid,<br />               SUM(
ps.hits) as numhits<br /> FROM primarystats AS ps<br />  INNER JOIN camp ON <a href="http://camp.id"
target="_blank">camp.id</a>= ps.idcamp<br />  INNER JOIN sites ON <a href="http://sites.id"
target="_blank">sites.id</a>= ps.idsite<br />WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHEREsitescategory.idsites = ps.idsites )<br /> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHEREsitescategory.idsites = ps.idsites )<br />GROUP BY ps.userid;<br /><br />Because I am rewriting this query to use
Greenplum,I cannot use correlated subqueries (they are not currently supported).<br /><br />Can anyone suggest a
versionthat will garner the same results? I tried with OUTER JOINS and some IS NULLs, but I couldn't get it right.<br
/><br/>Thanks!<br /><br />bricklen<br /><br /><br />

Re: Rewrite without correlated subqueries

From
Mark Fenbers
Date:
Try putting your subqueries into temporary tables, first, inside a BEGIN ... COMMIT block.  But your subqueries would
producethe negative, i.e., everything except where sitescategory.idsites = ps.idsites.  Then reference these temp
tablesin your query with inner or outer joins as appropriate.  Your new query would not include the ... IN (
<list>) syntax...<br /><br /> Mark<br /><br /> bricklen wrote: <blockquote
cite="mid:33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com"type="cite">Hi All,<br /><br /> I'm having some
troublewrapping my head around the syntax to rewrite a query using correlated subqueries, to using outer joins etc.<br
/><br/> The query:<br /><br /> SELECT  ps.userid,<br />                SUM( ps.hits ) as numhits<br /> FROM
primarystatsAS ps<br />   INNER JOIN camp ON <a href="http://camp.id" moz-do-not-send="true"
target="_blank">camp.id</a>= ps.idcamp<br />   INNER JOIN sites ON <a href="http://sites.id" moz-do-not-send="true"
target="_blank">sites.id</a>= ps.idsite<br /> WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHEREsitescategory.idsites = ps.idsites )<br /> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHEREsitescategory.idsites = ps.idsites )<br /> GROUP BY ps.userid;<br /><br /> Because I am rewriting this query to
useGreenplum, I cannot use correlated subqueries (they are not currently supported).<br /><br /> Can anyone suggest a
versionthat will garner the same results? I tried with OUTER JOINS and some IS NULLs, but I couldn't get it right.<br
/><br/> Thanks!<br /><br /> bricklen<br /><br /><br /></blockquote>

Re: Rewrite without correlated subqueries

From
bricklen
Date:
Interesting idea. Preferably this operation could be done in straight SQL in a single transaction, to fit in with the way our application works, but if that's not possible I may need to go the temporary table route.

On Thu, Aug 20, 2009 at 1:40 PM, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:
Try putting your subqueries into temporary tables, first, inside a BEGIN ... COMMIT block.  But your subqueries would produce the negative, i.e., everything except where sitescategory.idsites = ps.idsites.  Then reference these temp tables in your query with inner or outer joins as appropriate.  Your new query would not include the ... IN ( <list> ) syntax...

Mark


bricklen wrote:
Hi All,

I'm having some trouble wrapping my head around the syntax to rewrite a query using correlated subqueries, to using outer joins etc.

The query:

SELECT  ps.userid,
               SUM( ps.hits ) as numhits
FROM primarystats AS ps
  INNER JOIN camp ON camp.id = ps.idcamp
  INNER JOIN sites ON sites.id = ps.idsite
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE sitescategory.idsites = ps.idsites )
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE sitescategory.idsites = ps.idsites )
GROUP BY ps.userid;

Because I am rewriting this query to use Greenplum, I cannot use correlated subqueries (they are not currently supported).

Can anyone suggest a version that will garner the same results? I tried with OUTER JOINS and some IS NULLs, but I couldn't get it right.

Thanks!

bricklen



Re: Rewrite without correlated subqueries

From
Scott Marlowe
Date:
On Thu, Aug 20, 2009 at 3:16 PM, bricklen<bricklen@gmail.com> wrote:
> Interesting idea. Preferably this operation could be done in straight SQL in
> a single transaction, to fit in with the way our application works, but if
> that's not possible I may need to go the temporary table route.

Temp tables can be included in a transaction, and they're not visible
to other connections.

Re: Rewrite without correlated subqueries

From
bricklen
Date:
<div class="gmail_quote">On Thu, Aug 20, 2009 at 2:59 PM, Scott Marlowe <span dir="ltr"><<a
href="mailto:scott.marlowe@gmail.com">scott.marlowe@gmail.com</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im">On Thu,
Aug20, 2009 at 3:16 PM, bricklen<<a href="mailto:bricklen@gmail.com">bricklen@gmail.com</a>> wrote:<br /> >
Interestingidea. Preferably this operation could be done in straight SQL in<br /> > a single transaction, to fit in
withthe way our application works, but if<br /> > that's not possible I may need to go the temporary table route.<br
/><br/></div>Temp tables can be included in a transaction, and they're not visible<br /> to other connections.<br
/></blockquote></div><br/>Yeah I know, but I was thinking more of replacing this query with vanilla SQL. Maybe that's
justnot be feasible.<br /><br /><br />