Can an INNER JOIN offer better performance than EXISTS

SqlSql ServerSql Server-2005PerformanceTsql

Sql Problem Overview

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.

Sql Solutions

Solution 1 - Sql

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 - Sql

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 - Sql

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.

Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Sql Server-2005 Solutions
Recommended Performance Solutions
Recommended Tsql Solutions

Previous Solution:

Why can't you use null as a key for a Dictionary<bool?, string>?

C#.NetDictionaryHashtable

Next Solution:

Is there a way to detect if a debugger is attached to another process from C#?

C#Debugging

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJames WisemanView Question on Stackoverflow
Solution 1 - SqlQuassnoiView Answer on Stackoverflow
Solution 2 - SqlgbnView Answer on Stackoverflow
Solution 3 - SqlRoma RuzichView Answer on Stackoverflow