>>I replaced my LEFT OUTER JOIN with INNER JOIN, and the query took only 8 seconds this time.
>>
>>LEFT JOIN can always be replaced with INNER JOIN and a UNION. Did both INNER JOINS table1->table2 and table2->table1 work the same?
>
>
>I'm not sure, but that comment doesn't ring true to me. Can you provide an example test to show me what you mean?

;with CTE_Inner as (select T1.*, T2.Field from myTable T1 inner JOIN AnotherTable T2 on some condition)

select * from CTE_INNER ;
UNION ALL
select T1.*, cast statements for other fields from myTable T1 LEFT JOIN cte_Inner C on T1.ID = C.ID where C.ID IS NULL

This is an old thread, but I was just re-reading it and this is how I would solve it (replace LEFT JOIN with INNER JOIN and UNION).