Using MSSQL Server 2008 Enterprise Edition, and most likely other versions of MSSQL, here is a proof of concept that makes a temp table and materializes NEWID() differently depending on if you used a JOIN or LEFT JOIN, even though we are matching two rows exactly.
If you look at the execution plan, you can see that the compute scalar to get NEWID() is performed last using JOIN, but not when using LEFT JOIN. I would have expected the LEFT JOIN behavior. Is this weirdness due to naivety in the execution plan or is there something more going on?
Demonstration With 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 rows.
With Left Join, NewParentGuid is the same.
EDIT2: If you append this to the left join, the results change.
where temp2.ParentGuid = temp2.ParentGuid
Or as another user pointed out, where that column is not null. They'll stay the same when performing comparisons on other columns or where 1=1.Schroedinger's column?
See also:
Why does newid() materialize at the very end of a query?
Not really an answer but an observation
This returns 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
this forces them both to 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With