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

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" 

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.

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 

A LEFT OUTER JOIN will tend to perform better than a NOT EXISTS**, but in your case you want to do EXISTS and using a simple INNER JOIN doesn't exactly replicate the EXISTS behavior. If you have multiple Results for an Institution, doing the INNER JOIN will return multiple rows for that institution. You could get around that by using DISTINCT, but then the EXISTS will probably be better for performance anyway.

** For those not familiar with this method:

SELECT MyTable.MyTableID FROM dbo.MyTable T1 LEFT OUTER JOIN dbo.MyOtherTable T2 ON T2.MyTableID = T1.MyTableID WHERE T2.MyOtherTableID IS NULL 

is equivalent to

SELECT MyTable.MyTableID FROM dbo.MyTable T1 WHERE NOT EXISTS (SELECT * FROM MyOtherTable T2 WHERE T2.MyTableID = T1.MyTableID) 

assuming that MyOtherTableID is a NOT NULL column. The first method generally performs faster than the NOT EXISTS method though.

Are you using EXISTS as part of a correlated subquery? If so, the join will almost always be faster.

Your database should have ways of benchmarking queries. Use them to see which query runs faster.

If you want the institutions that did not have results, then a 'Where Not Exists' subquery will be faster, as it will stop as soon as it finds a single result for those that have results...

If you want the institutions With results, but you don't actually want the results, same thing. Use a 'Where Exists' subquery.. It will stop as soon as it finds a single result... This also ensures that the result set will only have one record per institution, whereas if you had an institution with multiple results, using the join approach would require that you add the 'distinct' keyword or a 'Group By' clause to eliminate the duplicate cartesion product rows that would be prodcued from the multiple Result records that matched to a single insitution.

If you need the Results, then do a JOIN - An Inner Join if you don't want to see the insitutions without results, and an outer join if you want to see ALL institutions, including the ones with no Results.

Actually, from your vague description of the problem, it sounds to me like a NOT IN query is the most obvious way to code it:

SELECT * FROM Institutions WHERE InstitutionID NOT IN ( SELECT DISTINCT InstitutionID FROM Results ) 

In cases like above the Exists statement works faster than that of Joins. Exists will give you a single record and will save the time also. In case of joins the number of records will be more and all the records must be used.

If the RESULTS table has more than one row per INSTITUTION, EXISTS() has the added benefit of not requiring you to select distinct Institutions.

As for performance, I have seen joins, IN(), and EXISTS() each be fastest in a variety of uses. To find the best method for your purposes you must test.

If you're referring to using a left (or right) outer join or a not exists subquery, I'm fairly certain the left outer join wins performance-wise. For example:

SELECT t1.* FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL

The above should be quicker than the equivalent sub-query, and if you're referring specifically to exists - well, where structure allows, an inner join will always be the preferred option.