I use CTEs now routinely instead of using sub-queries. Just a new way of doing it that seems more clear and structured to me.

So, instead of:

SELECT

r.Val

FROM

someTable AS r

WHERE

r.Val IN

(SELECT foo FROM someOtherTable)

Using CTE:

WITH

cteTest (foo)

AS (

SELECT foo FROM someOtherTable

),

cteResult (val)

AS (

SELECT

r.val

,t.foo

FROM someTable AS r

INNER JOIN cteTest AS t

ON r.val = t.foo

)

SELECT * FROM cteResult

CTEs can also be written to do recursive queries and that is a more complex subject.