SELECT subquery within IN statement hanging SQL Server -

curious behavior sql server.

this query produces results quickly, i'd expect:

select * dbo.v_view1 view1 full outer join     dbo.v_view2 view2 on view1.portfolio = view2.portfolio ,      view1.asofdate = view2.asofdate (view1.asofdate in (null, '20130717')) 

however, don't want have static date in there, replaced subquery. unfortunately, longest i've waited query execute 5 minutes before cancelled it, don't know if me data want:

select * dbo.v_view1 view1 full outer join      dbo.v_view2 view2 on view1.portfolio = view2.portfolio ,       view1.asofdate = view2.asofdate (view1.asofdate in (null, (select max(asofdate) dbo.v_view1))) 

i've resorted declaring variable, setting subquery above, , using in in statement, works expected , runs original query.

i know i'm doing wrong or missing (probably both) - it? i'd have subquery within in statement, or @ least able run view without variables. thanks!

i suspect query optimizer doing strange because naive implementation involves 2 scans on v_view1 perhaps optimizer failing realize subquery select max(asofdate) ... going same each row. suspect might failing realize subquery not correlated each row , running every row of result set. given full outer join, lot of data means lot of unnecessary table scans.

the simple solution is:

declare @maxasofdate datetime; set @maxasofdate = (select max(asofdate) dbo.v_view1)  select * dbo.v_view1 view1 full outer join     dbo.v_view2 view2 on view1.portfolio = view2.portfolio ,      view1.asofdate = view2.asofdate (view1.asofdate in (null, @maxasofdate)) 

and force subquery run once, store result in variable, , use following query.