Can an INNER JOIN offer better performance than EXISTS
Solution 1
Generally speaking, INNER JOIN
and EXISTS
are different things.
The former returns duplicates and columns from both tables, the latter returns one record and, being a predicate, returns records from only one table.
If you do an inner join on a UNIQUE
column, they exhibit same performance.
If you do an inner join on a recordset with DISTINCT
applied (to get rid of the duplicates), EXISTS
is usually faster.
IN
and EXISTS
clauses (with an equijoin correlation) usually employ one of the several SEMI JOIN
algorithms which are usually more efficient than a DISTINCT
on one of the tables.
See this article in my blog:
Solution 2
Maybe, maybe not.
- The same plan will be generated most likely
- An INNER JOIN may require a DISTINCT to get the same output
- EXISTS deals with NULL
Solution 3
In sql server 2019
queries with IN
, EXIST
, JOIN
statements have different plans (if correct indexes added). So performence also is different. It is shown in article https://www.mssqltips.com/sqlservertip/6659/sql-exists-vs-in-vs-join-performance-comparison/ that JOIN
is some faster.
P.S. I understand that question was about sql server 2005 (in tags), but people mostly looks for answer by article title.
![James Wiseman](https://i.stack.imgur.com/m0COC.jpg?s=256&g=1)
James Wiseman
I am a Microsoft certified C# developer working in the UK financial services industry. I started my career building software for a chemical weapons detection system for the British army before moving to Scotland to work in legal software. I now develop C# web-based applications in the pensions and life-assurance industry. I've written code since i was 9 years old, initially on my BBC Micro. I've used C#, jQuery, JavaScript. C++, SQL, VB and am proficient web development. I have presented courses in jQuery and have spoken at a local Techmeetup group on JavaScript static analysis. I run a web page and blog dedicated to discussions of my software development experiences. More recently, a thread of consumer-affairs related articles that I have written has featured in the Independent and Mirror newspapers. My personal blog is at http://www.jameswiseman.com/blog/ Twitter: @jameswiseman76 SOreadytohelp
Updated on July 05, 2022Comments
-
James Wiseman almost 2 years
I've been investigating making performance improvements on a series of procedures, and recently a colleague mentioned that he had achieved significant performance improvements when utilising an INNER JOIN in place of EXISTS.
As part of the investigation as to why this might be I thought I would ask the question here.
So:
- Can an INNER JOIN offer better performance than EXISTS?
- What circumstances would this happen?
- How might I set up a test case as proof?
- Do you have any useful links to further documentation?
And really, any other experience people can bring to bear on this question.
I would appreciate if any answers could address this question specifically without any suggestion of other possible performance improvements. We've had quite a degree of success already, and I was just interested in this one item.
Any help would be much appreciated.
-
EricI over 11 yearsThis is somewhat off topic, but I would suggest avoiding DISTINCT and use GROUP BY for overall better performance when returning distinct lists. DISTINCT doesn't perform as well as GROUP BY in general. It may help make up some of the difference between INNER JOIN and EXISTS as well.
-
Quassnoi over 11 years@EricI: could you please provide an example of a query which is less efficient with
DISTINCT
thanGROUP BY
, provided that the outputs are identical? Thanks!