Can I get better performance using a JOIN or using EXISTS?

Question

Depending on the statement, statistics and DB server it may make no difference - the same optimised query plan may be produced.

There are basically 3 ways that DBs join tables under the hood:

• Nested loop - for one table much bigger than the second. Every row in the smaller table is checked for every row in the larger.

• Merge - for two tables in the same sort order. Both are run through in order and matched up where they correspond.

• Hash - everything else. Temporary tables are used to build up the matches.

By using exists you may effectively force the query plan to do a nested loop. This may be the quickest way, but really you want the query planner to decide.

I would say that you need to write both SQL statements and compare the query plans. You may find that they change quite a bit depending on what data you have.

For instance if [Institutions] and [Results] are similar sizes and both are clustered on InstitutionID a merge join would be quickest. If [Results] is much bigger than [Institutions] a nested loop may be quicker.

It depends.

Ultimately the 2 serve entirely different purposes.

You JOIN 2 tables to access related records. If you don't need to access the data in the related records then you have no need to join them.

EXISTS can be used to determine if a token exists in a given dataset but won't allow you to access the related records.

Post an example of the 2 methods you have in mind and I might be able to give you a better idea.

---

With your two tables Institutions and Results if you want a list of institutions that have results, this query will be most efficient:

select Institutions.institution_name 
from Institutions
inner join Results on (Institutions.institution_id = Results.institution_id)

If you have an institution_id and just want to know if it has results, using EXISTS might be faster:

if exists(select 1 from Results where institution_id = 2)
  print "institution_id 2 has results"
else
  print "institution_id 2 does not have results"

Whether there's a performance difference or not, you need to use what's more appropriate for your purpose. Your purpose is to get a list of Institutions (not Results - you don't need that extra data). So select Institutions that have no Results... translation - use EXISTS.

It depends on your optimizer. I tried the below two in Oracle 10g and 11g. In 10g, the second one was slightly faster. In 11g, they were identical.

However, #1 is really a misuse of the EXISTS clause. Use joins to find matches.

select *
from
  table_one t1
where exists (
             select *
             from table_two t2
             where t2.id_field = t1.id_field
             )
order by t1.id_field desc


select t1.*
from 
  table_one t1
 ,table_two t2
where t1.id_field = t2.id_field
order by t1.id_field desc 

I'd say a JOIN is slower, because your query execution stops as soon as an EXISTS call finds something, while a JOIN will continue until the very end.

EDIT: But it depends on the query. This is something that should be judged on a case-by-case basis.