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.