JOIN or Correlated subquery with exists clause, which one is better

Question

select * 
from ContactInformation c 
where exists (select * from Department d where d.Id = c.DepartmentId )

select * 
from ContactInformation c 
inner join Department d on c.DepartmentId = d.Id  

Both the queries give out the same output, which is good in performance wise join or correlated sub query with exists clause, which one is better.

Edit :-is there alternet way for joins , so as to increase performance:- In the above 2 queries i want info from dept as well as contactinformation tables

Answer 1

Generally, the EXISTS clause because you may need DISTINCT for a JOIN for it to give the expected output. For example, if you have multiple Department rows for a ContactInformation row.

In your example above, the SELECT *:

• means different output too so they are not actually equivalent
• less chance of a index being used because you are pulling all columns out

Saying that, even with a limited column list, they will give the same plan: until you need DISTINCT... which is why I say "EXISTS"

Answer 2

You need to measure and compare - there's no golden rule which one will be better - it depends on too many variables and things in your system.

In SQL Server Management Studio, you could put both queries in a window, choose Include actual execution plan from the Query menu, and then run them together.

You should get a comparison of both their execution plans and a percentage of how much of the time was spent on one or the other query. Most likely, both will be close to 50% in this case. If not - then you know which of the two queries performs better.

You can learn more about SQL Server execution plans (and even download a free e-book) from Simple-Talk - highly recommended.