I have a query with a couple of CTEs. Only one CTE queries from the tables. All other CTES query this CTE and then finally joined up. E.g.

with MAIN_CTE as

(

  //Query from tables

),

CTE1 as

(

  // query from MAIN_CTE

),

CTE2 as

(

  // query from MAIN_CTE

)

Etc

Select * from

  CTE1

  Left outer join CTE2

  Left outer join CTE3

Etc

If I run MAIN_CTE on its own I get 152K indexed reads

If I run the full query, I get >300K indexed reads

So the question is, is the MAIN_CTE evaluated each time another CTE refers to it?