Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calling CTE multiple times in same query

I have a fairly complicated query with multiple CTEs but 1 main CTE that the others all pull from, does this cause that main CTE to be executed multiple times?

like image 891
Preston Avatar asked Aug 07 '13 04:08

Preston


People also ask

Can we call CTE multiple times?

Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler.

How do you use multiple CTE in one query?

The first CTE is separated from the second one by a comma. This also goes if you write more than two CTEs: all the CTEs are separated by a comma. However, no matter how many CTEs you have, there's no comma between the last CTE and the main query.

Can we use two CTE in a single select query?

After learning common table expressions or CTEs, a natural question is “Can I use several CTEs in one query?” Yes, you can!

How many times can I use a CTE?

A CTE can be recursive or non-recursive. A recursive CTE is a CTE that references itself. A recursive CTE can join a table to itself as many times as necessary to process hierarchical data in the table.


2 Answers

You could use CROSS JOIN thus:

SELECT 
    AVG(CASE WHEN instructorID = @instructorID THEN score END) InstructorSemesterAverage,
    STDEVP(CASE WHEN instructorID = @instructorID THEN score END) InstructorSemesterSTDeviation,
    AVG(CASE WHEN subjectCode = @subjectCode THEN score END) DepartmentSemesterAverage, 
    STDEVP(CASE WHEN subjectCode = @subjectCode THEN score END) DepartmentSemesterSTDeviation, 
    AVG(CASE WHEN bannerCRN=@CRN AND Q.year = @year AND semester = @semester THEN score END) ClassScore,
    STDEVP(CASE WHEN bannerCRN=@CRN AND Q.year = @year AND semester = @semester THEN score END) ClassSTDeviation,
    (SELECT DecTile FROM cteNtile WHERE instructorID = @instructorID)*10 DecTile,
    X.DepartmentClassFiveYearAverage AS DepartmentClassFiveYearAverage,
    X.DepartmentClassFiveYearSTDeviation AS DepartmentClassFiveYearSTDeviation,
    X.InstructorClassFiveYearAverage AS InstructorClassFiveYearAverage,
    X.InstructorClassFiveYearSTDeviation AS InstructorClassFiveYearSTDeviation
FROM 
    cteMain Q CROSS JOIN cteFiveYear X

This will prevent multiple executions (for actual execution plan see Number of Executions property) for cteFiveYear.

Example: If you execute this query

SELECT  h.ProductID,h.StandardCost,
        x.AvgPrice
FROM    Production.ProductCostHistory h
CROSS JOIN (
    SELECT  AVG(p.ListPrice) AvgPrice
    FROM    Production.Product p
) x

using AdventureWorks2008R2 database then the actual execution plan will be enter image description here

like image 102
Bogdan Sahlean Avatar answered Sep 21 '22 04:09

Bogdan Sahlean


Have a look at the below

DECLARE @Table TABLE(
        ID INT,
        Val VARCHAR(50),
        TypeID INT
)

DECLARE @TableTypes TABLE(
        TypeID INT,
        TypeName VARCHAR(50)
)

;WITh Vals AS (
        SELECT  *
        FROm    @Table
        WHERE   ID > 10
)
, UsingVals1 AS (
        SELECT  v.*,
                tt.TypeName
        FROm    Vals v INNER JOIN
                @TableTypes tt ON v.TypeID = tt.TypeID
)
, UsingVals2 AS (
        SELECT  v.*,
                tt.TypeName
        FROm    Vals v INNER JOIN
                @TableTypes tt ON v.TypeID = tt.TypeID
        WHERE   tt.TypeName LIKE '%%'
)
SELECT  *
FROM    UsingVals1
UNION 
SELECT  *
FROM    UsingVals2

And then at this SQL Fiddle DEMO

You will notice from the execution plan that the Vals CTE section is executed twice.

Maybe also have a look at How many times the T-SQL inside the CTE is executed?

like image 40
Adriaan Stander Avatar answered Sep 17 '22 04:09

Adriaan Stander