whether to use join or subquery

[zulu99]

Hi


Which approach to take.
We have certain sql queries. In first case we have used join for retrieving the results where as in the second case we use sub queries to get the same results.

Which one of them is better , faster & more effecient.
I have read that joins are always the slowest.

Bye

[sreddy]

Depends on volume of data, indexes, whether data is partitioned or not, init.ora parms for optimizer etc., I have 160 million row tables but data is partitioned, no issues with join queries. Its totally depends on the database and it can be tuned either way(joins/sub queries).Test the query performance in both ways and take it from there.

[Mnemonical]

Remeber that sometimes when you use a subquery, your query may be converted to join.


F.

[slimdave]

Originally posted by zulu99
Which one of them is better , faster & more effecient.

I would say that you are the onlly person to be able to answer that.

i) try it on your own data
ii) don't establish any rules of thumb
iii) keep it as simple as possible
iv) benchmark each query with each methodology
v) question averything people tell you unless they can back it up with solid proof