Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Join VS Subquery

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
richie_techin

New User


Joined: 21 Jan 2006
Posts: 16
Location: india

PostPosted: Tue Jan 31, 2006 11:10 am    Post subject: Join VS Subquery
Reply with quote

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
View user's profile Send private message

Hames

New User


Joined: 03 Oct 2005
Posts: 49

PostPosted: Tue Jan 31, 2006 4:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Hames

New User


Joined: 03 Oct 2005
Posts: 49

PostPosted: Tue Jan 31, 2006 4:45 pm    Post subject:
Reply with quote

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
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Tue Jan 31, 2006 7:39 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Hames

New User


Joined: 03 Oct 2005
Posts: 49

PostPosted: Wed Feb 01, 2006 5:39 pm    Post subject:
Reply with quote

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
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Wed Feb 01, 2006 7:06 pm    Post subject: Re: Join VS Subquery
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am
No new posts Join Keys and DB2 Query jackare SYNCSORT 2 Sat Feb 13, 2016 5:57 am
No new posts Join Keys to compare two files senthamizh DFSORT/ICETOOL 2 Fri Feb 05, 2016 8:28 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us