What is difference between JOINS and SUBQUERIES?Can anything we can do with joins can be done with subqueries also or vice-versa?

Question

Possible Duplicate:
SQL: Join vs. subquery

Is there anything that we can do with joins but not with subqueries or vice-versa?

Solution 1

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

Solution 2

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.

Comments

[onedaywhen]

"Generally speaking, Joins are faster and less expensive" -- But is there any value in generalizing?

[onedaywhen]

"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 using EXISTS (subquery) rather than a join, ditto semi difference and I note many people on SO like to use IN (subquery).

[Gautam Bhalla]

Is there any way to write correlated subqueries in oracle using join?

[Gautam Bhalla]

Is there any way to write correlated subqueries in oracle using join?