INNER JOIN vs Sub-Query (with where clause)

[islandfong]

Hi there,

I am implementing a reporting database which manipulating a huge amount of data. I used a lot of join. Just wondering which one performs better between the two scenarios:
1. INNER JOIN first then filter by the WHERE clause.
2. Sub-query which WHERE clause first then INNER JOIN.
Anybody have any recommendation?

[pragatiswain]

Hi there,

I am implementing a reporting database which manipulating a huge amount of data. I used a lot of join. Just wondering which one performs better between the two scenarios:
1. INNER JOIN first then filter by the WHERE clause.
2. Sub-query which WHERE clause first then INNER JOIN.
Anybody have any recommendation?

Subquery keeps the sql more clear.
May be you have to see the plan, cost and execution time of both the queries and choose the better one.

[islandfong]

As such, does it mean that there is not a general principle for using INNER JOIN or Sub-Query, at least there is not a clear cut line to say when is to use INNER JOIN and when is to use Sub-Query? But I do have this impression that the sub-query will surely add some slow down to the performance.

[suvam]

Can u please write down the exact query ?
Or U can do it yourself by identifying the driving table and where it is involved .

[birdready2fly]

Hi there,

I am implementing a reporting database which manipulating a huge amount of data. I used a lot of join. Just wondering which one performs better between the two scenarios:
1. INNER JOIN first then filter by the WHERE clause.
2. Sub-query which WHERE clause first then INNER JOIN.
Anybody have any recommendation?

sorry u r s question is not clear