Why does the left join cause NEWID () to be implemented earlier than the join?

Question

Using MSSQL Server 2008 Enterprise Edition and, most likely, other versions of MSSQL, is a proof of concept that makes a temporary table and materializes NEWID () differently depending on whether you use JOIN or LEFT JOIN, although we are juxtaposing the exact two rows .

If you look at the execution plan, you will see that the calculation scalar to get NEWID () is executed last using JOIN, but not when using LEFT JOIN. I would expect LEFT JOIN behavior. Is this weirdness due to naivety in terms of execution or is something else happening?

Demo using the Temp table:

Create Table #Temp ( ChildGuid uniqueidentifier, ParentGuid uniqueidentifier ) insert into #Temp (ChildGuid, ParentGuid) Values('5E3211E8-D382-4775-8F96-041BF419E70F', '96031FA0-829F-43A1-B5A6-108362A37701') insert into #Temp (ChildGuid, ParentGuid) Values('FFFFFFFF-D382-4775-8F96-041BF419E70F', '96031FA0-829F-43A1-B5A6-108362A37701') --Use a join. Get different NewIDs. select * from #Temp join ( select ParentGuid, NewParentGuid from( select ParentGuid, NEWID() as NewParentGuid from #Temp group by ParentGuid ) tb2 ) temp2 on #Temp.ParentGuid = temp2.ParentGuid --Do exactly as above, but use a left join. Get a pair of the same NewIDs. select * from #Temp left join ( select ParentGuid, NewParentGuid from( select ParentGuid, NEWID() as NewParentGuid from #Temp group by ParentGuid ) tb2 ) temp2 on #Temp.ParentGuid = temp2.ParentGuid 

With Join, NewParentGuid is different for both strings.

With Left Join, NewParentGuid is the same.

EDIT2: if you add this to the left join, the results will be changed.

 where temp2.ParentGuid = temp2.ParentGuid 

Or, as another user pointed out, where this column is not zero. They will remain unchanged when performing comparisons in other columns, or where 1 = column 1.Schroedinger?

See also:

Why does newid () materialize at the very end of the request?

Answer 1

Not exactly the answer, but observation

This returns a duplicate

 select * from #Temp inner hash join ( select ParentGuid, NEWID() as NewParentGuid from #Temp group by ParentGuid union select null, NEWID() ) temp2 on #Temp.ParentGuid = temp2.ParentGuid --Do exactly as above, but use a left join. Get a pair of the same NewIDs. select * from #Temp left hash join ( select ParentGuid, NEWID() as NewParentGuid from #Temp group by ParentGuid ) temp2 on #Temp.ParentGuid = temp2.ParentGuid 

it makes them both be different

 select * from #Temp join ( select ParentGuid, NEWID() as NewParentGuid from #Temp group by ParentGuid ) temp2 on #Temp.ParentGuid = temp2.ParentGuid --Do exactly as above, but use a left join. Get a pair of the same NewIDs. select * from #Temp left join ( select ParentGuid, NEWID() as NewParentGuid from #Temp group by ParentGuid ) temp2 on #Temp.ParentGuid = temp2.ParentGuid and temp2.ParentGuid is not null 

Answer 2

I don’t know why this was implemented at the end of the query for the inner join, but the behavior for the left join aslo changed if you put the where clause on it (effectively changing it to the inner join)

 select * from #Temp left join ( select ParentGuid, NewParentGuid from( select ParentGuid, NEWID() as NewParentGuid from #Temp group by ParentGuid ) tb2 ) temp2 on #Temp.ParentGuid = temp2.ParentGuid where temp2.ParentGuid is not null 

This can do this for any where clause. Hmm, it seems like if you want the GUID to work properly in the left join described above, it is safest to execute the view outside the selected object and put the results in the temp table. Then saving later will not accidentally change the way it works.