When comparing subqueries and joins, which one is faster?

    Requires Free Membership to View

The best answer is: it depends. It depends on which database system you're using. It depends on how sophisticated the optimizer is. It depends on which indexes you have defined on the columns. It depends on how complex the query is. It depends on whether the current month name has an R in it. Okay, I was kidding about that last one.

Here are two interesting articles that touch this issue in slightly more detail than I would:

The final answer? You will have to run performance tests on your own tables with your own subqueries and joins, and compare the results. That was my advice in NOT EXISTS correlated subquery, or OUTER JOIN? (20 September 2004).

That said, my preference is often for the subquery. A subquery just seems to convey the semantics of what the query is trying to achieve better than a join. For an example, see my previous answer Subquery or join? (20 October 2003).

This was first published in November 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: