3

What is quicker in execution - writing a join or doing a subquery? Like 

select * 
from table A 
where A.fieldAValue =(select fieldAValue from table B where fieldXValue =101) ;

My reasoning is that the decision to pick the right sql depends on how the data is structured /defined in the tables as well. Am I right or wrong?

Spear A1
  • 525
  • 1
  • 7
  • 20
  • Does this answer your question? [Join vs. sub-query](https://stackoverflow.com/questions/2577174/join-vs-sub-query) – philipxy Oct 16 '22 at 23:27

2 Answers2

9

There's really no way to answer this question. You're thinking about it wrong. A SQL query is not an instruction to the database about how to retrieve the result set you want, instead, it's a description of the data you want. How to get it is up to the database.

Oracle will rewrite your query into an execution plan. It will search for what it thinks is the most efficient plan based on your tables, indexes, constraints, the statistics it gathers, and even guesses it makes based on the data as it goes. In fact, it's very possible you can write this query with a join and with a subquery and it leads to the exact same execution plan and the same performance.

My suggestion is to write the query so that it is easy to understand what is going on. Only if the performance is actually poor should you worry about what it's doing. Oracle is pretty smart and may well find a route to your data that is better than you thought it would be.

What you have is fine and probably the query I would write myself to start with. You have not only a subquery but a scalar subquery, which had darn well better return exactly one or zero rows. So, if your fieldXValue is not unique in the table, you will get an exception.

Scalar subqueries are generally very good to use. They are cached, so the query will probably only be run once, even if there are a million rows in your result set. I would recommend this article (along with everything else Tom Kyte has ever written): On Caching and Evangelizing SQL

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
eaolson
  • 14,717
  • 7
  • 43
  • 58
1

There will be moments where a join is the ideal when dealing with large data. A table with just a few rows you can just use select and where as a subquery. It's best to know when to use each.

Jackal
  • 3,359
  • 4
  • 33
  • 78
  • 2
    Not always. For the given example from the question, you are right, but there are plenty of examples where a correlated sub-query or a "with" clause can be much faster; depending on the sizes of the tables, how many rows are selected from each table, etc. – Mark Stewart Mar 15 '19 at 19:28
  • A tool for each situation – Jackal Mar 15 '19 at 20:01
  • Thanks @MarkStewart and Jackal - So what would be the consensus /experts opinion on when to use a subquery and when to go with a join? – Spear A1 Mar 15 '19 at 20:42
  • Lots of variables on when to use which, but generally/usually/sometimes if one table is small (and your join columns are indexed on both tables) and one is huge, a correlated sub-query will be faster, but with all things Oracle there are no iron-clad rules, so best to test both. (And when testing, run each query a few times to "prime the pump"). – Mark Stewart Mar 18 '19 at 14:25