Page 1 of 1
Join query versus Sub query.
Posted: Tue Nov 17, 2015 11:22 pm
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.
Re: Join query versus Sub query.
Posted: Wed Dec 02, 2015 2:37 pm
by Prakash Jha
Is there no answer for this?
Re: Join query versus Sub query.
Posted: Wed Dec 02, 2015 3:25 pm
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
Re: Join query versus Sub query.
Posted: Thu Dec 03, 2015 2:39 pm
by Prakash Jha
enrico-sorichetti wrote:Is there no answer for this?
nobody likes You
Why?? I have not asked any stupid question!??
Re: Join query versus Sub query.
Posted: Thu Dec 03, 2015 2:48 pm
by enrico-sorichetti
did You notice the laughing icon I used ???
and it was part of the list of possible reasons
Re: Join query versus Sub query.
Posted: Thu Dec 03, 2015 8:35 pm
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!
Re: Join query versus Sub query.
Posted: Mon Mar 27, 2017 3:00 pm
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.
Re: Join query versus Sub query.
Posted: Tue Mar 28, 2017 6:13 pm
by Anuj Dhawan
You're welcome!