La estructura WITH AS se conoce como CTE (common table expression), y de forma similar a las tablas derivadas, vistas o tablas temporales, sirve para crear conjuntos de datos temporales dentro del ámbito de ejecución de una consulta SQL, pero con dos mejoras:
1. Las CTE permiten múltiples referencias sobre una misma definición, es decir, puedes crear un conjunto de datos temporal con una sola sentencia SQL y utilizarlo varias veces en el FROM, esto es una ventaja sobre las tablas derivadas, que requieren una definición por cada referencia, lo que genera código SQL más complejo.
2. Las CTE no son objetos, y las vistas o tablas temporales si lo son, en este caso, aunque también permitan múltiples referencias sobre una definición, tienen la desventaja de que se deben crear en la base de datos antes de formar parte de una consulta SQL.
Este ejemplo, sin mucho sentido, sirve para ver las diferencias.
Código:
--Selft join con tablas derivadas.
--Se codifica 2 veces la consulta sobre clientes
--para generar T1 y T2
select t1.nombre_cliente, t2.apellido_cliente
from
(select id_cliente, nombre_cliente, apellido_cliente
from clientes) as t1,
(select id_cliente, nombre_cliente, apellido_cliente
from clientes) as t2
where t1.id_cliente = t2.id_cliente
go
--Self join con vistas.
--Se codifica 1 consulta sobre clientes para generar T1 y T2
--pero se crea una vista
create view t as
select id_cliente, nombre_cliente, apellido_cliente
from clientes
go
select t1.nombre_cliente, t2.apellido_cliente
from t as t1, t as t2
where t1.id_cliente = t2.id_cliente
go
--Self join con tablas temporales.
--Se codifica 1 consulta sobre clientes para generar T1 y T2
--pero se crea una tabla temporal
insert into #t
select id_cliente, nombre_cliente, apellido_cliente
from clientes
go
select t1.nombre_cliente, t2.apellido_cliente
from #t as t1, #t as t2
where t1.id_cliente = t2.id_cliente
go
--Self join con tablas CTE.
--Se codifica 1 consulta sobre clientes para generar T1 y T2
--sin crear objetos
with t as (
select id_cliente, nombre_cliente, apellido_cliente
from clientes )
select t1.nombre_cliente, t2.apellido_cliente
from t as t1, t as t2
where t1.id_cliente = t2.id_cliente
go
En cuanto a rendimiento, es probable que tengas el mismo con CTE, vistas y tablas anidadas porque se resuelven con igual (o muy parecido) plan de ejecución, pero sea superior al de la tabla temporal.
Saludos