Avoiding repetition without creating a view

Question

Suppose that I have a query Q1 and I need to run a query like the following:

Q1
union
select *
from (some query that uses Q1 outcome)

I would like to do that:

• Without creating any view
• Without running twice the Q1 query.

How can I do that on PostgreSQL?

Answer 1

You can use Common Table Expressions for this:

WITH q1 AS
  ( SELECT ... )  
SELECT *
  FROM q1
UNION 
SELECT *
  FROM (some query that uses q1 outcome)
;

or even:

WITH q1 AS
  ( SELECT ... )  
TABLE q1
UNION 
SELECT *
  FROM (some query that uses q1 outcome)
;

You can also have more than one CTEs used in sequence:

WITH q1 AS
  ( SELECT ... )
  , q2 AS
  (some query that uses q1 outcome)  
  , q3 AS
  (some other query that uses q1 and/or q2 outcome)  
SELECT *
  FROM q1
UNION 
SELECT *
  FROM q2
UNION 
SELECT q3.*
  FROM q2 JOIN q3 ON ...
;

No view created and no running twice of the Q1 query - well, that's implementation dependent. What path of execution is actually followed depends on the optimizer and several other factors. The query is more compact and elegant but it doesn't mean it's more efficient than expanding it.