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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server