Support for nested WITH statements

TL;DR: nested WITH statements are supported in all modern data warehouses, lakehouses, and database engines except for Fabric & T-SQL products. This makes the usage of SQL templating engines unnecessarily complex.

Fabric supports regular WITH statements like the following:

with customers as (

  select *

  from lakehouseone.dbo.customers

),

orders as (

  select *

  from lakehousetwo.dbo.orders

)



select *

from orders o

join customers c

on o.customer_id = c.id

However, the following nested version is currently not supported:

with customers_with_addresses as (

  customers as (

    select *

    from lakehouseone.dbo.customers

  ),

  addresses as (

    select *

    from lakehousetwo.dbo.addresses

  ),

  final as (

    select c.id, c.name, a.line_1, a.zip, a.country

    from customers c

    join addresses a

    on c.address_id = a.id

  )

  select *

  from final

), orders as (

  select *

  from lakehousetwo.dbo.orders

)



select *

from orders o

join customers_with_addresses c

on o.customer_id = c.id      

You can see how working with CTEs allows for a very clean approach to SQL and enables the use of modular blocks of SQL.

This is especially popular in SQL templating engines like dbt.

Nested WITH statements allow for easy query injection. Wrapping a SELECT statement and inserting it into a query as a CTE only works if that inserted statement doesn't contain any CTEs.

More users have complained about the lack of nested WITH statements here and here.