2

I have always thought that Inner Join performs better than Outer JOIN, but I've experienced several cases now where this simply isn't true...

The other day I was creating a query with a series of INNER JOINS. Inner join was appropriate for all these joins as I only wanted rows matched in all joined tables.

I noticed that performance was very bad (1 min to run the query). The weird thing was that when I switched a few of them to Outer JOINs the query finished in a few seconds... It's not a case of warm up or caching because I restarted the SQL server in between runs, and the performance readings were consistent over time.

I experienced this same situation on two different reports, but the common theme was that performance was improved by switching to LEFT JOIN. I made the switch for the same tables for both reports. All JOINS were done ON GUID columns

Left JOIN gave the same number of rows since all ids were matched in the JOINED tables, so it was ok to switch, but I am curious if anyone has an explanation. Any advice on this?

The platform was SQL Server 2008 and all outer JOINS were LEFT JOINS

TGH
  • 38,769
  • 12
  • 102
  • 135
  • 3
    This is not an SQL question, it's a vendor-specific one. You should indicate which DBMS you were using (including as a tag). – paxdiablo Mar 30 '12 at 03:54
  • No, this is an SQL question having to do with relational algebra. Vendor specifics can impact performance but odds are there's an underlying reason why the OP's query performed better with an OUTER JOIN. – Larry Lustig Mar 30 '12 at 03:55
  • @TGH: you'll need to specify the actual query and the execution plans produced by the INNER and OUTER JOINs to get a good explanation of what happened. – Larry Lustig Mar 30 '12 at 03:56
  • Ok. Unfortunately I don't have it anymore. I was just wondering if there were any general guidelines for these things. It just didn't make any sense to me that I should use LEft JOIN as the tables were modeled appropriately for INNER JOIN – TGH Mar 30 '12 at 03:58
  • Without the query, my best guess is that the OUTER JOIN was able to filter the LEFT side of the query prior to performing the JOIN while the INNER JOIN deferred the filtering to afterwards. Would really need to see the query for a better guess. The execution plan would reveal all (well, much. . .) – Larry Lustig Mar 30 '12 at 04:03
  • And, except for the simplest cases, there is no good rule about one JOIN being faster than another. The execution plans become complex and depend on the size and cardinality of the JOIN and filter conditions. – Larry Lustig Mar 30 '12 at 04:04
  • Yes I guess it's true. I am just happy that LEFT JOIN could be used in this case as performance was a night and day difference. Usually I would never think to Outer JOIN unless it's needed. It was not a production query, so I didn't have the luxury of tailoring permanent indexes for it – TGH Mar 30 '12 at 04:07
  • possible duplicate of [INNER JOIN vs LEFT JOIN performance in SQL Server](http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server) – onedaywhen Mar 30 '12 at 08:14

1 Answers1

4

The inner join is faster than left join.

This answer should give you some tips.

Community
  • 1
  • 1
Iridio
  • 9,213
  • 4
  • 49
  • 71
  • Yes I guess it could be other factors interfering with the particular query... I'm not a dba, but I always assumed that OUTER was slower than INNER :-) – TGH Mar 30 '12 at 04:01