LEFT INNER JOIN vs. LEFT OUTER JOIN - Why does the OUTER take longer?

Question

We have the query below. Using a LEFT OUTER join takes 9 seconds to execute. Changing the LEFT OUTER to an LEFT INNER reduces the execution time to 2 seconds, and the same number of rows are returned. Since the same number of rows from the dbo.Accepts table are being processed, regardless of the join type, why would the outer take 3x longer?

SELECT CONVERT(varchar, a.ReadTime, 101) as ReadDate,        a.SubID,        a.PlantID,        a.Unit as UnitID,        a.SubAssembly,        m.Lot   FROM dbo.Accepts a WITH (NOLOCK) LEFT OUTER Join dbo.Marker m WITH (NOLOCK) ON m.SubID = a.SubID WHERE a.LastModifiedTime BETWEEN @LastModifiedTimeStart AND @LastModifiedTimeEnd    AND a.SubAssembly = '400' 

Answer 1

The fact that the same number of rows is returned is an after fact, the query optimizer cannot know in advance that every row in Accepts has a matching row in Marker, can it?

If you join two tables A and B, say A has 1 million rows and B has 1 row. If you say A LEFT INNER JOIN B it means only rows that match both A and B can result, so the query plan is free to scan B first, then use an index to do a range scan in A, and perhaps return 10 rows. But if you say A LEFT OUTER JOIN B then at least all rows in A have to be returned, so the plan must scan everything in A no matter what it finds in B. By using an OUTER join you are eliminating one possible optimization.

If you do know that every row in Accepts will have a match in Marker, then why not declare a foreign key to enforce this? The optimizer will see the constraint, and if is trusted, will take it into account in the plan.