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

1) in a query window in SQL Server Management Studio, run the command:

SET SHOWPLAN_ALL ON

2) run your slow query

3) your query will not run, but the execution plan will be returned. store this output

4) run your fast version of the query

5) your query will not run, but the execution plan will be returned. store this output

6) compare the slow query version output to the fast query version output.

7) if you still don't know why one is slower, post both outputs in your question (edit it) and someone here can help from there.

Answer 2

Wait -- did you actually mean that "the same number of rows ... are being processed" or that "the same number of rows are being returned"? In general, the outer join would process many more rows, including those for which there is no match, even if it returns the same number of records.

Answer 3

This is because the LEFT OUTER Join is doing more work than an INNER Join BEFORE sending the results back.

The Inner Join looks for all records where the ON statement is true (So when it creates a new table, it only puts in records that match the m.SubID = a.SubID). Then it compares those results to your WHERE statement (Your last modified time).

The Left Outer Join...Takes all of the records in your first table. If the ON statement is not true (m.SubID does not equal a.SubID), it simply NULLS the values in the second table's column for that recordset.

The reason you get the same number of results at the end is probably coincidence due to the WHERE clause that happens AFTER all of the copying of records.

Join (SQL) Wikipedia

Answer 4

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.