common table expression or subquery?

Question

Following there are two queries,providing the same result , with the only difference that the first query is with a subquery and the second one is with a common table expression. The question is what is the "performance" difference between those two ways (subquery and CTE) ?

From what I have read CTE is better when you want to reuse the query but, apart from that I haven't read any other advantage or disadvantage between those two ways.

SELECT col1,col2,col3,tt.c1,tt.c2.tt.c3
FROM table1 t1
INNER JOIN (SELECT t2.col1 as c1 ,t2.col2 as c2 ,t3.col3 as c3
            FROM table2 t2
            INNER JOIN table3 t3 ON t2.col1= t3.col1
            WHERE t3.col1>100 ) tt ON t1.col1= tt.col1

;WITH CTE(
SELECT t2.col1 as c1 ,t2.col2 as c2 ,t3.col3 as c3
FROM table2 t2
INNER JOIN table3 t3 ON t2.col1= t3.col1
WHERE t3.col1>100
)


SELECT col1,col2,col3,tt.c1,tt.c2.tt.c3
FROM table1 t1
INNER JOIN CTE  tt ON t1.col1= tt.col1

Why not run them both and look at the execution plan? That is what is required to know what the performance differences would be. — Sean Lange, Oct 30 '15 at 16:56
Tagging 3 different database engines isn't going to help. Anyway, this question is far too broad. — DavidG, Oct 30 '15 at 16:56

score 0 · Answer 1 · answered Oct 30 '15 at 16:59

0

In this context and with this usage, it's essentially the same. You would use the CTE only to help readability.

answered Oct 30 '15 at 16:59

Yan Brunet

4,727
2
25
35

common table expression or subquery?

1 Answers1