Join query versus Sub query.
-
- Registered Member
- Posts: 62
- Joined: Sat Jun 29, 2013 1:45 pm
Join query versus Sub query.
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.
Please let me know your thoughts.
-
- Registered Member
- Posts: 62
- Joined: Sat Jun 29, 2013 1:45 pm
-
- Global Moderator
- Posts: 826
- Joined: Wed Sep 11, 2013 3:57 pm
Re: Join query versus Sub query.
we reply on our own time and free of chargeIs there no answer for this?
if nobody replies it means that
nobody has an in depth knowledge of the topic
nobody is interested in answering
nobody likes You
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
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
-
- Registered Member
- Posts: 62
- Joined: Sat Jun 29, 2013 1:45 pm
Re: Join query versus Sub query.
Why?? I have not asked any stupid question!??enrico-sorichetti wrote:nobody likes YouIs there no answer for this?
-
- Global Moderator
- Posts: 826
- Joined: Wed Sep 11, 2013 3:57 pm
Re: Join query versus Sub query.
did You notice the laughing icon I used ???
and it was part of the list of possible reasons
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
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
- Anuj Dhawan
- Founder
- Posts: 2802
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: Join query versus Sub query.
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!
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.
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.
-
- Registered Member
- Posts: 62
- Joined: Sat Jun 29, 2013 1:45 pm
Re: Join query versus Sub query.
Great explanation and Thanks for the answer.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!
- Anuj Dhawan
- Founder
- Posts: 2802
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: Join query versus Sub query.
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.
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.
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