Join query versus Sub query.

RDBMS from IBM.
Previous topicNext topic

Topic Author
Prakash Jha
Registered Member
Posts: 43
Joined: Sat Jun 29, 2013 1:45 pm
Zodiac: Sagittarius

Join query versus Sub query.

Post by Prakash Jha » Tue Nov 17, 2015 11:22 pm

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.




Topic Author
Prakash Jha
Registered Member
Posts: 43
Joined: Sat Jun 29, 2013 1:45 pm
Zodiac: Sagittarius

Re: Join query versus Sub query.

Post by Prakash Jha » Wed Dec 02, 2015 2:37 pm

Is there no answer for this?




enrico-sorichetti
Global Moderator
Global Moderator
Posts: 892
Joined: Wed Sep 11, 2013 3:57 pm

Re: Join query versus Sub query.

Post by enrico-sorichetti » Wed Dec 02, 2015 3:25 pm

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-)


Topic Author
Prakash Jha
Registered Member
Posts: 43
Joined: Sat Jun 29, 2013 1:45 pm
Zodiac: Sagittarius

Re: Join query versus Sub query.

Post by Prakash Jha » Thu Dec 03, 2015 2:39 pm

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: 892
Joined: Wed Sep 11, 2013 3:57 pm

Re: Join query versus Sub query.

Post by enrico-sorichetti » Thu Dec 03, 2015 2:48 pm

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: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: Join query versus Sub query.

Post by Anuj Dhawan » Thu Dec 03, 2015 8:35 pm

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.


Topic Author
Prakash Jha
Registered Member
Posts: 43
Joined: Sat Jun 29, 2013 1:45 pm
Zodiac: Sagittarius

Re: Join query versus Sub query.

Post by Prakash Jha » Mon Mar 27, 2017 3:00 pm

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: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: Join query versus Sub query.

Post by Anuj Dhawan » Tue Mar 28, 2017 6:13 pm

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.

Previous topicNext topic

Return to “IBM DB2.”