Test table:
CREATE TABLE t1 ( col1 int, col2 int, ... );
Subquery
SELECT * FROM t1 WHERE col1=2
Is it OK to use this subquery two times in same statement or should temp
table created to prevent subquery
executing twice?
Which is better
SELECT *
(
SELECT * FROM (SELECT * FROM t1 WHERE col1=2) p1 WHERE col2=3
UNION ALL
SELECT * FROM (SELECT * FROM t1 WHERE col1=2) p2 WHERE col2=4
) p3
GROUP BY 1;
or
CREATE TEMP TABLE temp ON COMMIT DROP AS SELECT * FROM t1 WHERE col1=2;
SELECT *
(
SELECT * FROM temp p1 WHERE col2=3
UNION ALL
SELECT * FROM temp p2 WHERE col2=4
) p3
GROUP BY 1
?
In real query select statements above contain several tables and have more
sophisticated where clauses.
Using PostgreSQL 8.0+
Andrus.