CTE and UNION Queries

I have a question regarding the use of CTEs.

The query I'm working on involves a UNION of four or five smaller queries, each doing different things. Each query, however, uses the same base set of records. eg:

SELECT ...
FROM Products p
JOIN ... -- many other tables
WHERE p.ProductIsActive = 1

UNION

SELECT ...
FROM Products p
JOIN ... -- a different set of tables
WHERE p.ProductIsActive = 1

UNION ... -- etc etc

So each subquery is working from the same base set of Products rows.

My idea was to pull that out into a CTE. Something like this:

WITH ActiveProducts AS (
SELECT ProductID
FROM Products
WHERE ProductIsActive = 1
)
SELECT ...
FROM ActiveProducts p
JOIN ... -- many other tables

UNION

SELECT ...
FROM ActiveProducts p
JOIN ... -- a different set of tables

UNION ... -- etc etc

So my question is: If I do it this way, will the "ActiveProducts" CTE get executed for every subquery? Or will it just get executed once and used like a view by each subquery?

Or is there a better way to do this that I'm overlooking?

Thanks!

The CTE should behave just like a view. The best way to see how it will optimize is to look at the plan of the query.

So it should be like:

create view ActiveProducts AS
SELECT ProductID
FROM Products
WHERE ProductIsActive = 1
go

SELECT ...
FROM ActiveProducts p
JOIN ... -- many other tables

UNION

SELECT ...
FROM ActiveProducts p
JOIN ... -- a different set of tables

UNION ... -- etc etc

Or:

SELECT ...
FROM (SELECT ProductID
FROM Products
WHERE ProductIsActive = 1) p
JOIN ... -- many other tables

UNION

SELECT ...
FROM (SELECT ProductID
FROM Products
WHERE ProductIsActive = 1) p
JOIN ... -- a different set of tables

|||you might consider creating a temporary table for your CTE to eliminate multiple execution of the same query.

declare @.ActiveProducts table (ProductId int)

INSERT INTO @.ActiveProducts
SELECT ProductId
FROM Products
WHERE ProductIsActive = 1

SELECT ...
FROM @.ActiveProducts p
JOIN ... -- many other tables

UNION

SELECT ...
FROM @.ActiveProducts p
JOIN ... -- a different set of tables


HTH,