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;