My Technical Notes

Friday, 31 May 2013

Common Table Expression (CTE) vs Subquery Syntax

In a lot of cases, a Common Table Expression (CTE) can be replaced with a subquery. However, CTE's are more powerful in the sense that they can be referenced mutiple times within a query, whereas a subquery can only be referenced once.

Common Table Expression:


; WITH xs AS 
(
    SELECT 1 x UNION SELECT 2 UNION SELECT 3
)
SELECT x FROM xs

Subquery:


SELECT x FROM (SELECT 1 x UNION SELECT 2 UNION SELECT 3) xs

It is worth noting that in the subquery example, the subquery comes immediately before the alias.

No comments: