Joins versus EXISTS

Compare Joins to EXISTS(<subquery>) constructs.

Background

At RDBMS level, joins are often faster than EXISTS constructs because, if there are no records in the joined table that satisfy the query, with joins this is detected at an early stage. However, EXISTS submitted to the Rules Engine supplies a number of optimization strategies.

When to use

If a subquery can be executed separately of the main query (that is, the main table(s) queried are not referenced in the subquery) then use EXISTS rather than a join, because the Rules Engine will then be able to perform the subquery separately and be able to optimize.

If a subquery cannot be executed separately to the main query (that is, the main table(s) queried are referenced in the subquery), then write a join if the tables are not too small and if the statement is processed on the server. Databases can almost always handle join statements faster than subqueries if the number of records is rather large.