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

Question

I have two tables Institutions and Results and I want to see if there are any results for institutions that way I can exclude the ones that don't have results.

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

Thank you,
-Nimesh

Answer 1

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.