Thread: Reuse of Subselects

Hi all,

if I have something like this:

SELECT column1,
       (... complicated subselect ...),
       column1 - (... same subselect as above ...)
FROM table1;

do I really have to rewrite the subselect a 2nd time if I need that
result in another column's expression?

Depend in the exact query, you can do:

SELECT column1, x.c, column1-x.c
FROM table1, (... complicated subselect ...) as x;

The above may not work if they're correlated, so you can try:

SELECT column1, column2, column1-column2
FROM
  (SELECT column1, (... complicated subselect ...) as column2
   FROM table1);

Hope this helps,

On Tue, Feb 17, 2004 at 01:02:43PM +0100, Holger Marzen wrote:
> Hi all,
>
> if I have something like this:
>
> SELECT column1,
>        (... complicated subselect ...),
>        column1 - (... same subselect as above ...)
> FROM table1;
>
> do I really have to rewrite the subselect a 2nd time if I need that
> result in another column's expression?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> If the Catholic church can survive the printing press, science fiction
> will certainly weather the advent of bookwarez.
>    http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow

temporary tables work.  Save the complicated
subselect in temporary table, following
queries just simple select on temp table.

Holger Marzen wrote:
> Hi all,
>
> if I have something like this:
>
> SELECT column1,
>        (... complicated subselect ...),
>        column1 - (... same subselect as above ...)
> FROM table1;
>
> do I really have to rewrite the subselect a 2nd time if I need that
> result in another column's expression?

--
P. J. "Josh" Rovero                                 Sonalysts, Inc.
Email: rovero@sonalysts.com    www.sonalysts.com    215 Parkway North
Work: (860)326-3671 or 442-4355                     Waterford CT 06385
***********************************************************************

On Tue, Feb 17, 2004 at 13:02:43 +0100,
  Holger Marzen <holger@marzen.de> wrote:
> Hi all,
>
> if I have something like this:
>
> SELECT column1,
>        (... complicated subselect ...),
>        column1 - (... same subselect as above ...)
> FROM table1;
>
> do I really have to rewrite the subselect a 2nd time if I need that
> result in another column's expression?

You should be able to use a join. The syntax won't be simpler, but you
might get nearly a 2 fold speed up.

Assuming that the subselect depends on the current row, you want to do
something like:

SELECT (
  SELECT column1, complicated, column1 - complicated FROM
    (complicated select AS complicated) AS comptable)
  ) FROM table1;