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

Improve this question
8
  • 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). Commented Mar 30, 2012 at 3: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. Commented Mar 30, 2012 at 3: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. Commented Mar 30, 2012 at 3: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 Commented Mar 30, 2012 at 3: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. . .) Commented Mar 30, 2012 at 4:03

1 Answer 1

Reset to default
4

The inner join is faster than left join.

This answer should give you some tips.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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 :-)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.