Entendendo e usando CTE (Common Table Expression)

As CTE�s s�o muito semelhantes �s tabelas derivadas, mas com uma boa vantagem�

De acordo com o BOL a estrutura para uma CTE �:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
SELECT 
FROM expression_name;

WITH expression_name [ ( column_name [,...n] ) ]

( CTE_query_definition )

SELECT

FROM expression_name;

Uma compara��o de uso entre uma tabela derivada e uma CTE:

Tabela derivada:

SELECT *
FROM
  (SELECT Nome, Cidade
   FROM CLIENTES 
   WHERE DataCadastro &gt; '2011-01-01') AS CLI2011

SELECT *

FROM

(SELECT Nome, Cidade

FROM CLIENTES

WHERE DataCadastro > '2011-01-01') AS CLI2011

Usando CTE:

;WITH CLI2011
AS
( SELECT Nome, Cidade
  FROM CLIENTES 
  WHERE DataCadastro &gt; '2011-01-01')
SELECT * FROM CLI2011;

;WITH CLI2011

( SELECT Nome, Cidade

FROM CLIENTES

WHERE DataCadastro > '2011-01-01')

SELECT * FROM CLI2011;

Note o ‘;’ antes do WITH… Ele est� l� por eu considerar uma boa pr�tica, uma vez que sempre que voc� utilizar uma CTE, caso tenha alguma outra consulta antes, ela obrigatoriamente deve terminar com o ‘;’. Ent�o, por via das d�vidas, sempre coloque ele antes do WITH, quando for usar uma CTE.

At� a�, nada demais, certo?

Mas a coisa melhora agora… quando falamos em m�ltiplas CTE’s x m�ltiplas tabelas derivadas

Se voc� precisa utilizar mais de uma tabela derivada e referenciar uma dentro de outra (aninhamento de tabelas), o c�digo pode ficar terr�vel de ler, por mais bem documentado que ele seja. Um exemplo (bem esdr�xulo) usando aninhamento de tabelas derivadas:

 SELECT *
 FROM
   (
     SELECT Estado, SUM(Valor) AS Val
     FROM (
            SELECT Estado, Valor 
            FROM PEDIDOS
          ) AS PD1
     GROUP BY Estado
   ) AS GRP_PD1
 WHERE Estado = 'BA'

SELECT *

FROM

(

SELECT Estado, SUM(Valor) AS Val

FROM (

SELECT Estado, Valor

FROM PEDIDOS

) AS PD1

GROUP BY Estado

) AS GRP_PD1

WHERE Estado = 'BA'

Agora, a mesma consulta, usando CTE:

;WITH PD1 AS
(
  SELECT Estado, Valor 
  FROM PEDIDOS 
),
  GRP_PD1 AS
(
  SELECT Estado, SUM(Valor) AS Val
  FROM PD1
  GROUP BY Estado
)
SELECT Estado, Val 
FROM GRP_PD1 
WHERE Estado = 'BA'