reuse a subquery
От | Charles Holleran |
---|---|
Тема | reuse a subquery |
Дата | |
Msg-id | BAY126-W18B41FAF70D0D6A8FB8B9BCE940@phx.gbl обсуждение исходный текст |
Ответы |
Re: reuse a subquery
Re: reuse a subquery |
Список | pgsql-novice |
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;
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;
В списке pgsql-novice по дате отправления: