|
View previous topic :: View next topic
|
| Author |
Message |
richie_techin
New User
Joined: 21 Jan 2006
Posts: 16
Location: india
|
|
|
|
hi,
Can anyone tell me why the JOIN is more preffered than SUBQUERY?
what is the difference between SUBQUERY andf CO-RELATED SUBQUERY? |
|
| Back to top |
|
 |
Hames
New User
.jpg)
Joined: 03 Oct 2005
Posts: 49
|
|
|
|
| Quote: |
| what is the difference between SUBQUERY andf CO-RELATED SUBQUERY? |
Sub query is the better than correlated subquery in the following manner.
1> sub queries are much faster.
2> Subqueries follow bottom up approach where as correlated subqueries follow both top-down as well as bottom up approach.
3> correlated subqueries normally used for querying in the same table like finding the second maximum salary or n th maximum salary in the employees table.
4> subqueries will first execute then the control given to the main query.
Where as in Correlated subquery, first a value is selected from main query then the sub query will be executed for all rows in subquery table. Then a second value is selected from the main query again sub query will be executed for all rows in subquery table. and upto the last record available in main query it goes on the same way.
That's why correlated subqueries are taking much time. |
|
| Back to top |
|
|
Hames
New User
Joined: 03 Oct 2005
Posts: 49
|
|
|
|
| Quote: |
| Can anyone tell me why the JOIN is more preffered than SUBQUERY? |
For subqueries only one option is available that's whatever condition you give, you get the results for that condition.
But in the case of JOIN we have two type of joins. INNER JOIN and OUTER JOIN.
INNER JOIN is simply like the same as a subquery, it will give the result for the given condition.
In OUTER JOIN we have three types. (I) LEFT OUTER JOIN (II) RIGHT OUTER JOIN (III) FULL OUTER JOIN
In LEFT OUTER JOIN, you will get the results for the given condition in addition to that the remaining rows in the first table which are not satisfied the condition also will be included in the result.
In RIGHT OUTER JOIN, you will get the results for the given condition in addition to that the remaining rows in the Second table which are not satisfied the condition also will be included in the result.
In FULL OUTER JOIN, you will get the results for the given condition in addition to that the remaining rows in both the table which are not satisfied the condition also will be included in the result.
If you go thru an example you will easily understand these concepts. Go thru the tutorials for example. IF you did not fine let me know I will send you the simple examples.
You can check this details in IBM DB2 UDB V 8.1 CERTIFICATION 700 Book written by Roger Sanders |
|
| Back to top |
|
|
nuthan
Active User
Joined: 26 Sep 2005
Posts: 146
Location: Bangalore
|
|
|
|
Hi,
In subquery first the inner query will execute, then based on the result of inner query outerquery will execute.
In correlated subquery for each row of outer query inner query will execute one time. |
|
| Back to top |
|
|
Hames
New User
Joined: 03 Oct 2005
Posts: 49
|
|
|
|
| Quote: |
| In correlated subquery for each row of outer query inner query will execute one time. |
Hi Nuthan,
This statement is not correct.
In correlated subquery for each row of outer query inner query will execute all rows in the table specified in the inner query. |
|
| Back to top |
|
|
nuthan
Active User
Joined: 26 Sep 2005
Posts: 146
Location: Bangalore
|
|
|
|
Hi ,
i told 'one time' in the sence like one time of all the rows which will satisfy the inner query. Any how thanks for correcting my sentence. |
|
| Back to top |
|
|
|
|
