I was also able to duplicate your results using a LEFT OUT JOIN. Interesting results in the execution plan.

Code first:

select

c.operator,

c.unit,

--c.unitcompleted, -- No column named this in the DDL provided

--(qcOperator, SN, grFPDate, haOperator, hadateFP, hsOperator, dateinspectedFP)

(select s.qcOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as GR_Operator,

(select s.grFPDate from dbo.Test_assemblyQC s where s.SN = c.SN) as GR_Date,

(select s.hsOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as HS_Operator,

(select s.dateinspectedFP from dbo.Test_assemblyQC s where s.SN = c.SN) as HS_Date,

(select s.haOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as HA_Operator,

(select s.hadateFP from dbo.Test_assemblyQC s where s.SN = c.SN) as HA_Date,

case when exists (select s.SN from dbo.Test_assemblyQC s where s.SN = c.SN) then 'Scanned' else 'Not Scanned' end as Inspected

from

dbo.Test_completedUnit c

where

c.operator = 'VERHEY, CHARLES' and

c.Workorder = '17879';

select

c.operator,

c.unit,

--c.unitcompleted, -- No column named this in the DDL provided

--(qcOperator, SN, grFPDate, haOperator, hadateFP, hsOperator, dateinspectedFP)

GR_Operator,

GR_Date,

HS_Operator,

HS_Date,

HA_Operator,

HA_Date,

case when oa.SN is not null then 'Scanned' else 'Not Scanned' end as Inspected

from

dbo.Test_completedUnit c

OUTER APPLY(select s.qcOperator, s.grFPDate, s.haOperator, s.hadateFP, s.hsOperator, s.dateinspectedFP, s.SN from dbo.Test_assemblyQC s where s.SN = c.SN) oa (GR_Operator,

GR_Date,

HA_Operator,

HA_Date,

HS_Operator,

HS_Date,

SN)

where

c.operator = 'VERHEY, CHARLES' and

c.Workorder = '17879';

select

c.operator,

c.unit,

--c.unitcompleted, -- No column named this in the DDL provided

--(qcOperator, SN, grFPDate, haOperator, hadateFP, hsOperator, dateinspectedFP)

s.qcOperator GR_Operator,

s.grFPDate GR_Date,

s.hsOperator HS_Operator,

s.dateinspectedFP HS_Date,

s.haOperator HA_Operator,

s.hadateFP HA_Date,

case when s.SN is not null then 'Scanned' else 'Not Scanned' end as Inspected

from

dbo.Test_completedUnit c

left outer join dbo.Test_assemblyQC s

on (s.SN = c.SN)

where

c.operator = 'VERHEY, CHARLES' and

c.Workorder = '17879';

Also, I modified the test tables so that SN had the same data definition between the tables, nvarchar(16).

I ran the above code twice, once with no index on the SN column for table [dbo].[Test_assemblyQC] and then again after creating a clustered index on the SN column on the table.