Possible Duplicate:
SQL: Join vs. subquery
Is there anything that we can do with joins but not with subqueries or vice-versa?
Asked
Active
Viewed 1.8k times
3
Community
- 1
- 1
Gautam Bhalla
- 1,170
- 2
- 10
- 16
-
That kind of question shouldn't be here. Doesn't make a lot of sense to begin with. – Erwin Brandstetter Oct 09 '11 at 18:30
-
What are the similarities? :-) – DavidEG Oct 09 '11 at 18:30
2 Answers
2
In most cases with enterprise applications, it's not just a question of IF something can be done, but HOW it is done. Generally speaking, Joins are faster and less expensive than subqueries. Ton of other posts on this subject on SO. Here's one: Join vs. sub-query
-
"Generally speaking, Joins are faster and less expensive" -- But is there any value in generalizing? – onedaywhen Oct 10 '11 at 07:33
-
Is there any way to write correlated subqueries in oracle using join? – Gautam Bhalla Oct 16 '11 at 19:37
1
Frankly, I can't think of a case where one thing can't be achieved from either method (subquery or join).
To me it's more about readability and performance. For example, a subquery might be slower whereas a join might take advantage of certain indexes. At least from the DBMS standpoint, I would imagine, should be easier to optimize a join when analyzing the expression.
Consider the case of several joins expressed as subqueries, for example. To most people, seeing the statement expressed as a Left, Right or Inner join would make it easier to understand and maintain. I even avoid using implicit joins since they hide the intent. In other words, I prefer to express a join as from table a inner join table b on a.id=b.id vs from table a, table b where a.id=b.id. Implementing joins as subqueries make it even less readable, IMO.
Icarus
- 63,293
- 14
- 100
- 115
-
"To most people, seeing the statement expressed as a Left, Right or Inner join would make it easier to understand and maintain" -- I'm not sure that is correct for most, certainly not for me e.g. I much prefer to write a [semijoin](http://stackoverflow.com/questions/7033748/what-kind-of-join-do-i-need/7036894#7036894) using `EXISTS (subquery)` rather than a join, ditto [semi difference](http://stackoverflow.com/questions/7494586/get-non-existing-data-from-another-table-join/7496263#7496263) and I note many people on SO like to use `IN (subquery)`. – onedaywhen Oct 10 '11 at 07:42
-
Is there any way to write correlated subqueries in oracle using join? – Gautam Bhalla Oct 16 '11 at 19:38