0

What is the best way to write a query out of these 2 methods? 1. Use of a sub query 2. Use of joins

Which way is faster and better when writing queries?

Thank you.

Shashika

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Shashika Virajh
  • 8,497
  • 17
  • 59
  • 103
  • 2
    Your question is too broad. If you have a specific query in mind, please include that in the question. – Gordon Linoff Jan 29 '16 at 22:10
  • Usually a join, but depends on what you're doing. – Nikki9696 Jan 29 '16 at 22:15
  • 1
    Question is also a duplicate of [this](http://stackoverflow.com/questions/2577174/join-vs-sub-query) and [this](http://stackoverflow.com/questions/3856164/sql-joins-vs-sql-subqueries-performance) – Boris Jan 29 '16 at 22:15

2 Answers2

2

In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.

In JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.

The good thing in sub-queries is that they are more readable than JOINs: that’s why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too.

— Kronas, Stackoverflow

Cannon
  • 168
  • 1
  • 6
  • 1
    Your statement is simply not true. Sometimes subqueries are faster. Sometimes joins are faster. Often, the execution plans are the same. The details of the query and the database structure are also very important considerations. – Gordon Linoff Jan 29 '16 at 22:30
  • Below is a nice book that goes a little into the subject. Yes, sometimes sub queries are faster; however, generally joins are faster, so it is probably best to try both scenarios. Here is a link to a good query performance book for SQL Server. https://books.google.com/books?id=3branTEOAEYC&pg=PA347&dq=subquery+vs+joins&hl=en&sa=X&ved=0ahUKEwik14nCjdDKAhUqmoMKHd3xBog4ChDoAQggMAE#v=onepage&q=subquery%20vs%20joins&f=false – Cannon Jan 29 '16 at 22:58
1

Coming from a perspective of someone who's done a lot of tuning and fixing legacy code, I prefer joins (and when possible, with conditions on the ON clause). It can really improve readability, especially on the procedures that are regularly expanded.

Bistabil
  • 183
  • 7