Re: Reuse of Subselects

On Tue, Feb 17, 2004 at 13:02:43 +0100,
Holger Marzen <holger(at)marzen(dot)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;