Thanks GSquared - this makes sense to me.

I don't think I agree about the clarity of using CTEs for derived tables, but I guess that might be because I still live in a SQL Server 2000 world (with very strict formatting standards).

I think I'd rather have:

SELECT Column1

FROM (

SELECT Column1

FROM (

SELECT Column1, Column2

FROM SomeTable

WHERE Column2 = 3

) AS Derived1

WHERE Column1 = 2

) AS Derived2

WHERE 1 = 1

than

WITH Derived1 AS (

SELECT Column1, Column2

FROM SomeTable

WHERE Column2 = 4

),

Derived2 AS (

SELECT Column1

FROM Derived1

WHERE Column1 = 3

)

SELECT Column1

FROM Derived2

WHERE 1 = 1

but if I understand correctly that is really a matter of taste / formatting rather than any logical difference (and I just checked the query plan for good measure :))