1

I come across several instances where I can write a query with using both joins or sub queries. I usually use joins but sometimes use sub queries (without any reason). I have read in several places (including stackoverflow) that joins are faster than sub queries in many instances but sometimes subqueries are faster. Right now the queries I am writing does not deal with large amount of data so I guess the speed isn't much of a concern. But for future, I'm curious about the following.

a.) Why are joins faster than subqueries (in general).

b.) What are the instances when subqueries are faster. How will I know?

c.) If I'm writing a query, how should I judge whether I should use subquery or a join. I will appreciate if someone explains me with an example.

khellang
  • 17,550
  • 6
  • 64
  • 84
Ank
  • 6,040
  • 22
  • 67
  • 100

3 Answers3

3

Saying that joins are 'mostly faster' than sub-queries is not true. This entirely depends an the DBMS used.

For Microsoft SQL Server I know that this is not true. Usually, the performance the the same. Not only in theory, but also in practice.

For MySQL I have heard that sub-queries are problematic. I don't have personal evidence.

Oracle seems to be about the same as SQL Server.

usr
  • 168,620
  • 35
  • 240
  • 369
  • Thanks for your answer. Before I write a query whats the best way to judge whether a join should be used or a subquery should be used. for any dbms. how do u judge? – Ank Mar 05 '12 at 00:16
  • Cannot be judged for any dbms. Please tell us a specific one. – usr Mar 05 '12 at 00:17
  • say MySQL. What I meant was how do you decide when you do it. Or is it for this DBMS use joins for this DBMS use subqueries etc – Ank Mar 05 '12 at 00:34
  • If the performance of queries are database dependent and we migrate from one database to other, does it mean that we have to re-write the queries completely if our database is large. – Ank Mar 05 '12 at 00:38
  • Oh that is true! In this case you should vastly prefer joins! – usr Mar 05 '12 at 18:11
2

The answers to your questions.

a) Joins aren't faster then subqueries (in general). But often DBMSs produce a much smarter execution plan if you use joins. This is related two the procedure how queries are transformed into execution plans.

b) c) In general there are no rules for writing fast queries. Furthermore, there is only one way to choose the correct query for your task: You have to benchmark the different versions. So if you have to decide how to formulate a certain query benchmark the first and if it performs good, then stop. Else change something and benchmark it again and if it is fine, then stop. Use an environment that is close to your production environment: use realistic datasets. A query might perform well with thousands of records but not with millions. Use the same hardware as in production. Consider to benchmark the query in the context of your application, since other queries of these may influence the performance of it.

gregor
  • 4,733
  • 3
  • 28
  • 43
1

The main reason from the research I've done is that the compiler more directly utilizes the proper indexes when you explicitly state how to do the join (i.e. left join, inner join, etc.) If you use a sub-query, you are leaving it a bit up to the optimizer and it not always does the fastest way (which is retarded as its called an 'optimizer').

Anyway, it may be easier to write your sub-query, but if you are building a query for speed and long-term use, its clear that you should write out the explicit joins.

Here are some links with some views and examples:

Join vs. Subquery

Another link This ones gives some details why joins are faster (in most cases) than sub-queries.

more examples

Community
  • 1
  • 1
jroyce
  • 2,029
  • 2
  • 22
  • 45
  • Your links are specific for SQL-Server, MySQL and DB2. They are not generally about SQL and Join-vs-subqueries performance. – ypercubeᵀᴹ Mar 04 '12 at 23:53