Reputation: 839

Join query or subquery

Are there rules of thumb for developers when to use join instead of subquery or are they the same.

Upvotes: 5

Answers (8)

dkretz

Reputation: 37655

The first principle is "State the query accurately". The second principle is "state the query simply and obviously" (which is where you usually make choices). The third is "state the query so it will process efficiently".

If its a dbms with a good query processor, equivalent query designs should should result in query plans that are the same (or at least equally efficient).

My greatest frustration upon using MySQL for the first time was how conscious I had to be to anticipate the optimizer. After long experience with Oracle, SQL Server, Informix, and other dbms products, I very seldom expected to concern myself with such issues. It's better now with newer versions of MySQL, but it's still something I end up needing to pay attention to more often than with the others.

Upvotes: 5

Milen A. Radev

Reputation: 62663

Let's ignore the performance impact for now (as we should if we are aware that "Premature optimization is the root of all evil").

Choose what looks clearer and easier to maintain.

Upvotes: 2

HLGEM

Reputation: 96640

In SQL Server a correlated subquery usually performs worse than a join or, often even better for performance, a join to a derived table. I almost never write a subquery for anything that will have to be performed multiple times. This is because correlated subqueries often basically turn your query into a cursor and run one row at a time. In databases it is usually better to do things in a set-based fashion

Upvotes: 1

Alex

Reputation: 43

As with many things, it depends. - how complex is the subquery - in a query how often is the subquery executed

I try to avoid subqueries whenever I can. Especially when expecting large result sets never use subqueries - in case the subquery is executed for each item of the result set.

take care, Alex

Upvotes: 2

Seb

Reputation: 25157

Problem with subqueries is that you might end having a sub-resultset without any key, so joining them would be more expensive.

If possible, always try to make JOIN queries and filter with ON clause, instead of WHERE (although it should be the same, as modern engines are optimized for this).

Upvotes: 3