What''s faster - Inner or Outer Join
-
I have a complex query with multiple joins.
What would be faster?
- All Inner Joins
- All Outer Joins with Where clauses to filter out the "NULL" records?
-
That is a very subjective question.
It depends on how the tables and columns are indexed, what join operater is used, etc.
Examples:
are both columns in the join predeicate indexed at all, clustered, noclustered or both. Are you able to make use of a covering index?
My advice is to try them both with STATISTICS IO ON and see what gives you the fewest reads. Run them both side by side and compare query plans.
If you really want to joins in depth, I highly recommend Craig Freedman's Blog:
http://blogs.msdn.com/craigfr/archive/tags/Joins/default.aspx
SQL guy and Houston Magician
-
No there is nothing subjective about the question because there is just one answer that is the INNER JOIN because OUTER JOIN as he original poster stated default to a mathematical NULL. And I looked at the article you referenced but most of those JOINs are just used by the Query Processor internally. The only relevant query was the Subquery it is equivalent to the INNER JOIN in speed. All the factors you referenced are usually isolated to either an INNER JOIN or OUTER JOIN because the NULL value restricts table placement in the OUTER JOIN to be fixed. Hope this helps.
Kind regards,
Gift Peddie -
As stated INNER as you don't need to filter for NULL, as well only the records needed are pulled into memory on both sides and index can be used on both more effectively. But the OUTER can vary between LEFT and RIGHT depeneding on the WHERE clause conditions in case you wonder about between those.
-
Good Points Gift Peddie and Antares686. For some reason, I was thinking in join operators. Sorry guys!
SQL guy and Houston Magician
-
Actually, they would be almost the same with the outer join a little slower.
The only difference here is the time consumed by the Query Optimizer to figure out an execution plan. For the inner join statement, the Query Optimizer already knows what to do while for the outer join, the Optimizer will still figure out how to perform the operation.
Check out their execution plans, they are the same. The Optimizer realizes that the same routine will generate the result. But if you check the Statistics (time), the outer join makes the Optimizer think more.
-
You must be talking about the obsolete syntax but still used by SQL Server 2000 WHERE clause JOIN, I am talking about the current JOIN using the FROM and ON clause with the WHERE clause just a filter as the AND operator.
The current JOIN operation they cannot be compared by ANSI SQL definition NULL checking and fixed table position makes optimization limited with the OUTER JOIN compared to the INNER JOIN. Check the link posted by the second poster in the thread for current syntax. Hope this helps.
Kind regards,
Gift Peddie -
Not the old syntax...
USE Northwind
select * from Customers c join orders o on c.Customerid = o.Customerid -- INNER JOIN
select * from Customers c left join orders o on c.Customerid = o.Customerid where o.Customerid is not null -- OUTER JOIN and filter out the null values
Try running this in QA with Show Execution Plan checked and compare the results.
-
Yes your syntax is current but You just run both INNER and OUTER JOIN on the same tables, that does not meet the ANSI SQL definition of OUTER JOIN because one table is supposed to be smaller than the other, that is the reason for the default NULL.
The above gives INNER JOIN a flexibility that let you use all optimization available by changing table position to use existing indexes and other optimization but OUTER JOIN you must use the optimization as allowed by your fixed table position and Null checking. If used in the context of a real database compared to just 13 tables Northwind the difference is significant.
Kind regards,
Gift Peddie
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply