2

Question 1: regarding performance, which is the best: subqueries or JOINs?

Question 2: is there any way to measure and compare the time when running the subquery or JOIN?

Edit

I am a bit confused, though less than earlier. I found this http://www.akadia.com/services/sqlsrv_subqueries.html

It wrap it up pretty well in combination with the answers below.

  • 2
    http://stackoverflow.com/questions/2577174/join-vs-sub-query – sqluser May 03 '15 at 15:00
  • 1st answer: Why don't you just try it yourself? 2nd answer: [Yes, there is.](http://bit.ly/1DW0NDC) – Zohar Peled May 03 '15 at 15:02
  • 2
    3rd answer: focus first on what you actually need. Sometimes you have to use a correlated subquery and a join won't do. Performance won't matter in that case. – siride May 03 '15 at 16:55
  • 1
    Please read: http://stackoverflow.com/questions/3856164/sql-joins-vs-sql-subqueries-performance?rq=1 (read further down the thread). – Per76 May 03 '15 at 20:02

4 Answers4

2

There is no generic answer. It depends on the platform you are using (Microsoft SQL Server, Oracle, MySQL, etc.) as well as the query.

As PM 77-1 said, optimizers these days are pretty good at resolving the relational algebra of a query to the most efficient execution plan. But each server and each query are different. In SQL Server you can look at the Estimated Execution plan to compare different queries and see how the optimizer will choose to execute it. Most platforms have a similar concept, like EXPLAIN.

This topic is very broad and requires a lot of experimenting with each platform and each database and query. Getting good at performance tuning is an art worth exploring though.

Joseph Gagliardo
  • 763
  • 5
  • 3
1

Subqueries vs. JOINs

JOINs are generally faster assuming the fields on which JOIN is performed are indexed. Sub-queries can be useful to extract a very smaller subset based on an index and join to the main table. In my experience, JOINs have been superior performance-wise. Different databases may optimize JOINs and sub-queries differently. You may get varying results on SQL Server and MySQL (and their different versions). So measurement/estimation of performance is essential, which brings us to the next question

Measure/estimation time

My preference is to do EXPLAIN and EXPLAIN EXTENDED on query with JOIN and sub-query to get an idea of how the DB (assuming, MySQL) is using indexes. Run the queries against current dataset and then create a bigger dummy dataset 3-5 times the size of your current and run the queries against them on a non-production system. That will give you the number of seconds the query takes to run.

On SQL Server, when you do explain, you will see cost numbers. You could compare those for JOIN'ed query vs. sub-query'ed query and then perform a run with curreent dataset and a dataset 3-5 times (or 10 times) larger than the current to see how your queries perform.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • 2
    Note: on SQL Server, subqueries are often implemented as joins, so the results are the same regardless of the syntax you use. It's important to note that in the execution plan. – siride May 03 '15 at 16:54
1

These days RDBMS have pretty smart and powerful optimizers. They will automatically decide between JOINs and sub-queries, regardless of your exact code. Do not start manual optimization until you actually experience performance problems.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
0

There are a lot of opinions about JOINs vs Subqueries.

Chris London has a great article on this subject.

So it seems like the verdict is to do subqueries. The reason the subquery in the join is faster than the subquery in the where clause is, I believe, because when it’s in the where it has to run that condition for every row whereas it only has to run it once for the subquery/join. Like I said before different RDBMSs handle things differently but even if your RDBMS doesn’t handle subqueries as well others, to me, they are more readable. So now I recommend subqueries!

Source: http://www.chrislondon.co/joins-vs-subqueries/

Per76
  • 184
  • 1
  • 1
  • 15