sql server - Why are the execution plans for these two queries so different? -

i have database following existing table in it:

create table [dbo].[sites] (     [site_id] [uniqueidentifier] not null,     [description] [nvarchar](80) not null,     [parent_id] [uniqueidentifier] null,     [domain_id] [int] not null,      constraint [pk_sites] primary key nonclustered ( site_id] asc )  alter table [dbo].[sites] check    add constraint [fk_sites_sites]    foreign key([parent_id]) references [dbo].[sites] ([site_id])  alter table [dbo].[sites] check constraint [fk_sites_sites] 

i have added table looks this:

create table [dbo].[site_map] (     [force_id] [int] not null,     [source_id] [int] not null,     [camera_id] [int] not null,     [site_id] [uniqueidentifier] not null,      constraint [pk_site_map] primary key clustered          ([force_id] asc, [source_id] asc, [camera_id] asc) )  alter table [dbo].[site_map] check    add constraint [fk_site_map_sites]    foreign key([site_id]) references [dbo].[sites] ([site_id])  alter table [dbo].[site_map] check constraint [fk_site_map_sites] 

now, when execute query:

select      m.[site_id], c.[description], s.[site_id] readerid, s.[domain_id]      [dbo].[site_map] m left outer join      [dbo].[sites] c on m.[site_id] = c.[site_id] left outer join      [dbo].[sites] s on c.[parent_id] = s.[site_id]      [force_id] = @forceid      , [source_id] = @sourceid      , [camera_id] = @cameraid 

i query plan executes 2 nested loops. but, when change query reads this:

select      m.[site_id], c.[description], s.[site_id] readerid, s.[domain_d]      [dbo].[site_map] m inner join      [dbo].[sites] c on m.[site_id] = c.[site_id] inner join      [dbo].[sites] s on c.[parent_id] = s.[site_id]      [force_id] = @forceid      , [source_id] = @sourceid      , [camera_id] = @cameraid 

the query plan executes 4 nested loops! thing changed type of join. why query plans different?

are noticing performance difference?

my guess inner joins still more efficient. take @ "number of executions" of each nested loop operation in query plan. i'm guessing left joins have naive nested loop operations, or @ least less efficient scan input, , inner joins have less executions , may not full cartesian loop of left side x right side, may executing once per row in left side. hard without seeing query plan.

i set statistics io on, flush caches , see how many logical reads each operation , see difference looks like.