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?
Best Answer
You can use Common Table Expressions for this:
or even:
You can also have more than one CTEs used in sequence:
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.