Skip site navigation (1) Skip section navigation (2)

Re: reuse a subquery

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: reuse a subquery
Date: 2010-08-09 15:21:45
Message-ID: i3p6e7$cd2$1@dough.gmane.org (view raw or flat)
Thread:
Lists: pgsql-novice
Charles Holleran wrote on 09.08.2010 16:04:
> I have a query that uses the same subquery twice.  What is the correct
> syntax to reuse the subquery instead of running it twice? The query
> below 'works' but reruns the identical subquery. The point of the
> subquery is to limit the join work to the subset of table_a where c = 3
> instead of the entire table_a with c ranging from 0 to 65535. The
> planner helps expedite the rerun query, but there must be a better
> syntax for subquery reuse.
>
> E.g.
>
> SELECT *
>
> FROM
> (
> SELECT *
> FROM table_a
> WHERE c = 3
> ORDER BY d
> ) AS T1
>
> LEFT JOIN
>
> (
> SELECT *
> FROM table_a
> WHERE c = 3
> ORDER BY d
> ) AS T2
>
> ON T2.d = (T1.d + 5)
> WHERE T2.d IS NULL
> ORDER BY T1.d;
>

What about:

WITH temp_a (col1, col2, col3) AS
(
    SELECT col1, col2, col3
    FROM table_a
    WHERE c = 3
)
SELECT *
FROM temp_a t1
   JOIN temp_a t2 ON (t2.d = t1.d + 5)
WHERE t2.d IS NULL
ORDER BY t1.d;

Regards
Thomas



In response to

pgsql-novice by date

Next:From: Oliveiros d'Azevedo CristinaDate: 2010-08-09 15:22:47
Subject: Re: reuse a subquery
Previous:From: Thom BrownDate: 2010-08-09 14:54:49
Subject: Re: reuse a subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group