Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am new to DB2 and I have a question about the with clause. For example in the following query:

WITH values AS 
      SELECT user_id, user_data FROM USER WHERE user_age < 20
SELECT avg(values.user_data) FROM values
UNION
SELECT sum(values.user_data) FROM values
How many times will the common table expression be executed? Will the result of the with clause be stored in a temporary table or it will do sub-select twice.
(I use with and union here just to give an example, and sorry for my poor english)
In DB2, common table expressions should create the Common Table Expression Node in the execution plan (see the documentation here).  This node explicitly says:
  
They serve as intermediate tables. Traditionally, a nested table
  expression also serves this purpose. However, a common table
  expression can be referenced multiple times after it is instantiated;
  nested table expressions cannot.
I read this as saying that the CTE is only evaluated once, instantiated, and then used multiple times.  Also, if the CTE is referenced only one time, the "instantiation" is optimized away.
Note that this is the way that Postgres handles CTEs (materialized subqueries) and not the way the SQL Server handles them.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.