44

I am working on rewriting some poorly written SQL queries and they are over-utilizing sub-queries. I am looking for best-practices regarding the use of sub-queries.

Any help would be appreciated.

Brad Krusemark
  • 503
  • 1
  • 4
  • 10
  • 2
    It's not that there isn't a silver bullet -- the issue is that the question is too broad to be reasonably answered. There's no guarantee that what works on one database (MySQL), works exactly the same on another. The simplest rule is that if column(s) from the subquery are in the final result set, use a JOIN -- it is the most optimal means of getting data from two tables. Otherwise, a subquery is fine but correlated ones can cause problems (though not with EXISTS). – OMG Ponies Jan 26 '11 at 00:37
  • 1
    On what basis do you state that sub-queries are being over utilized? It seems odd to decide that _and then_ ask for information about best practices for using sub-queries. – Larry Lustig Jan 26 '11 at 01:02
  • 2
    Possible duplicate of [Join vs. sub-query](http://stackoverflow.com/questions/2577174/join-vs-sub-query) – Ciro Santilli OurBigBook.com Jun 12 '16 at 19:16
  • 1
    @OMGPonies Your simplest rule matches AskTom, "they (joins/subqueries) are semantically different. * Use a subquery when you need no columns from the tables referenced in the subquery. * Use a join when you do need some of the columns. select * from emp where deptno in ( select deptno from dept ); would be "better" than select emp.* from emp, dept where emp.deptno = dept.deptno; And remember, a subquery cannot simply be replaced by a join (and vice versa), since they often result in DIFFERENT ANSWERS. " – ExcessOperatorHeadspace May 14 '19 at 18:13

3 Answers3

59

Subqueries are usually fine unless they are dependent subqueries (also known as correlated subqueries). If you are only using independent subqueries and they are using appropriate indexes then they should run quickly. If you have a dependent subquery you might run into performance problems because a dependent subquery typically needs to be run once for each row in the outer query. So if your outer query has 1000 rows, the subquery will be run 1000 times. On the other hand an independent subquery typically only needs to be evaluated once.

If you're not sure what is meant by a subquery being dependent or independent here's a rule of thumb - if you can take the subquery, remove it from its context, run it, and get a result set then it's an independent subquery.

If you get a syntax error because it refers to some tables outside of the subquery then its a dependent subquery.

The general rule of course has a few exceptions. For example:

  • Many optimizers can take a dependent subquery and find a way to run it efficiently as a JOIN. For example an NOT EXISTS query might result in an ANTI JOIN query plan, so it will not necessarily be any slower than writing the query with a JOIN.
  • MySQL has a bug where an independent subquery inside an IN expression is incorrectly identified as a dependent subquery and so a suboptimal query plan is used. This is apparently fixed in the very newest versions of MySQL.

If performance is an issue then measure your specific queries and see what works best for you.

Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • By correlated do you mean containing a reference to a column in the outer query? – El Ronnoco Jan 25 '11 at 23:06
  • 3
    "Many optimizers can take a dependent subquery and find a way to run it efficiently" -- I agree this statement, and it negates your whole argument i.e. "Correlated subqueries are bad except when they are not bad". – onedaywhen Sep 21 '11 at 10:40
7

There is no silver bullet here. Each and every usage has to be independently assessed. There are some cases where correlated subqueries are plain inefficient, this one below is better written as a JOIN

select nickname, (select top 1 votedate from votes where user_id=u.id order by 1 desc)
from users u

On the other hand, EXISTS and NOT EXISTS queries will win out over JOINs.

select ...
where NOT EXISTS (.....)

Is normally faster than

select ...
FROM A LEFT JOIN B
where B.ID is null

Yet even these generalizations can be untrue for any particular schema and data distribution.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
4

Unfortunately the answer greatly depends on the sql server you're using. In theory, joins are better from a pure-relational-theory point of view. They let the server do the right thing under the hood and gives them more control and thus in the end can be faster. If the server is implemented well. In practice, some SQL servers perform better if you trick it into optimizing it's queries through sub-queries and the like.

Wes Hardaker
  • 21,735
  • 2
  • 38
  • 69
  • Some sub-queries can also be promoted to joins (server-depending, of course). For most cases I find joins "fit my head" better. –  Jan 25 '11 at 23:03
  • No, if a server is implemented well it will recognize the fastest way to perform either a JOIN or a sub-query and produce the optimal execution plan. If the JOIN and the sub-query are algebraically the same, then a well implemented server will produce the same execution plan. – Larry Lustig Jan 26 '11 at 01:01
  • Yes... All true. If a server is implemented perfectly it'll always optimize your broken queries too ;-) But... that was most of my point: nearly all sql servers do better if you construct your queries according to how it behaves. It's an unfortunate truth. Generally, the longer a product has been around the more likely it is to do a better job no matter how you structure your queries. But even that isn't always true. – Wes Hardaker Jan 26 '11 at 16:19
  • 1
    "joins are better from a pure-relational-theory point of view" -- nonsense. Consider that SQL wasn't relationally complete as regards Codd's algebra until support for subqueries was added. – onedaywhen Sep 21 '11 at 10:46