Join query versus Sub query.

RDBMS from IBM and IBM's Hierarchical DBMS and Transaction Manager (IMS DC).
Post Reply
Prakash Jha
Registered Member
Posts: 56
Joined: Sat Jun 29, 2013 1:45 pm

Join query versus Sub query.

Post by Prakash Jha »

Is there a way to tell that for what kind of queries a join is recommended and when a sub query is recommended? Is there some stated rule or a way to determine this?

Please let me know your thoughts.
Prakash Jha
Registered Member
Posts: 56
Joined: Sat Jun 29, 2013 1:45 pm

Re: Join query versus Sub query.

Post by Prakash Jha »

Is there no answer for this?
enrico-sorichetti
Global Moderator
Global Moderator
Posts: 825
Joined: Wed Sep 11, 2013 3:57 pm

Re: Join query versus Sub query.

Post by enrico-sorichetti »

Is there no answer for this?
we reply on our own time and free of charge

if nobody replies it means that
nobody has an in depth knowledge of the topic
nobody is interested in answering
nobody likes You :D
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort 8-)
Prakash Jha
Registered Member
Posts: 56
Joined: Sat Jun 29, 2013 1:45 pm

Re: Join query versus Sub query.

Post by Prakash Jha »

enrico-sorichetti wrote:
Is there no answer for this?
nobody likes You :D
Why?? I have not asked any stupid question!??
enrico-sorichetti
Global Moderator
Global Moderator
Posts: 825
Joined: Wed Sep 11, 2013 3:57 pm

Re: Join query versus Sub query.

Post by enrico-sorichetti »

did You notice the laughing icon I used ???

and it was part of the list of possible reasons
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort 8-)
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: Join query versus Sub query.

Post by Anuj Dhawan »

There had been a long debate about this subject. One of the reasons is that different RDBMS behave differently from this question's per se.

Per the book DB2 SQL Tuning Tips for z/OS Developers by Tony Andrews, DB2 V9 may transform subqueries to whichever type it deems more efficient, especially when a subquery cannot be transformed in to a join. DB2 may choose to transform a subquery into a join, handling the duplicates. This can lead to some confusion when you look at a DB2 Explain and see a join or a different type of subquery than that was originally coded. DB2 chooses to correlate, de-correlate, or transform in to a join based on the cost.

What is said in above is not really true for MySQL or similar products (I did not find references for this) - said that, that's why the answer to this question ends up in "it depends". Personally, I find sub-queries are easily readable and more easily maintainable than JOINs.

A better approach, when you need to chosse one between the two, is to run Explain and see the costs for JOINs and subqueries in question and decide.


PS.: Enrico was not criticizing you, he was just being playful! :)
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Prakash Jha
Registered Member
Posts: 56
Joined: Sat Jun 29, 2013 1:45 pm

Re: Join query versus Sub query.

Post by Prakash Jha »

Anuj Dhawan wrote: There had been a long debate about this subject. One of the reasons is that different RDBMS behave differently from this question's per se.

Per the book DB2 SQL Tuning Tips for z/OS Developers by Tony Andrews, DB2 V9 may transform subqueries to whichever type it deems more efficient, especially when a subquery cannot be transformed in to a join. DB2 may choose to transform a subquery into a join, handling the duplicates. This can lead to some confusion when you look at a DB2 Explain and see a join or a different type of subquery than that was originally coded. DB2 chooses to correlate, de-correlate, or transform in to a join based on the cost.

What is said in above is not really true for MySQL or similar products (I did not find references for this) - said that, that's why the answer to this question ends up in "it depends". Personally, I find sub-queries are easily readable and more easily maintainable than JOINs.

A better approach, when you need to chosse one between the two, is to run Explain and see the costs for JOINs and subqueries in question and decide.


PS.: Enrico was not criticizing you, he was just being playful! :)
Great explanation and Thanks for the answer.
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: Join query versus Sub query.

Post by Anuj Dhawan »

You're welcome! :)
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “IBM DB2 and IMS DB/DC”