Utilizing a Right and Left Outer Joins in same SELECT
I am looking to better build my SQL Development skill set. Where I work currently most all the data fits into a hierarchy and we almost always start with the top most element to work our way "down". This results in really only needing to use INNER and LEFT OUTER joins for the various queries.
I am hoping to get some guidance on a situation where I might need to use a RIGHT OUTER and a LEFT OUTER JOIN in the same query.
Referencing a previous question on StackOverflow What is the difference between Left, Right, Outer and Inner Joins? I understand what a RIGHT OUTER and a LEFT OUTER JOIN is, however all problems I have been asked to solve, could be solved using only INNER and LEFT OUTER joins.
EDIT:
I did find that this is basically a duplicate of a previous StackOverflow question When or why would you use a right outer join instead of left?. It would appear that in general there is a consensus that
- Most all queries using a
RIGHT OUTER JOINcan be converted into an
INNERandLEFT OUTERversion. - There are some benefits for
read-ability formatting or organizational processes which might
require using aRIGHT OUTERinstead of aLEFT OUTER.
There is also an answer on that question which details a particular situation where you would INNER JOIN a group of tables together and then RIGHT OUTER JOIN to find where the "right" table has records in the whole INNER JOIN'ed subset. I think this can be accomplished with an INNER JOIN and LEFT OUTER JOIN version it would just require some work.
sql-server
asked 6 hours ago
Kirk SaundersKirk Saunders
534
add a comment |
I am looking to better build my SQL Development skill set. Where I work currently most all the data fits into a hierarchy and we almost always start with the top most element to work our way "down". This results in really only needing to use INNER and LEFT OUTER joins for the various queries.
I am hoping to get some guidance on a situation where I might need to use a RIGHT OUTER and a LEFT OUTER JOIN in the same query.
Referencing a previous question on StackOverflow What is the difference between Left, Right, Outer and Inner Joins? I understand what a RIGHT OUTER and a LEFT OUTER JOIN is, however all problems I have been asked to solve, could be solved using only INNER and LEFT OUTER joins.
EDIT:
I did find that this is basically a duplicate of a previous StackOverflow question When or why would you use a right outer join instead of left?. It would appear that in general there is a consensus that
- Most all queries using a
RIGHT OUTER JOINcan be converted into an
INNERandLEFT OUTERversion. - There are some benefits for
read-ability formatting or organizational processes which might
require using aRIGHT OUTERinstead of aLEFT OUTER.
There is also an answer on that question which details a particular situation where you would INNER JOIN a group of tables together and then RIGHT OUTER JOIN to find where the "right" table has records in the whole INNER JOIN'ed subset. I think this can be accomplished with an INNER JOIN and LEFT OUTER JOIN version it would just require some work.
sql-server
asked 6 hours ago
Kirk SaundersKirk Saunders
534
You should describe the "situation where I might need to use aRIGHT OUTERand aLEFT OUTER JOINin the same query".
– MDCCL
5 hours ago
@MDCCL That is what the question is. Can we provide guidance on when this would ever be required?
– Martin Smith
5 hours ago
@MartinSmith So that means that the OP wants someone to describe such a situation, that is, provide an example? I'm interpreting the issue as something like "I'm involved in a situation where I might need to use aRIGHT OUTER... in the same query, so I want guidance on it", but it looks like you are understanding the question correctly.
– MDCCL
5 hours ago
@kirk Could you confirm Martin's interpretation?
– MDCCL
5 hours ago
Martin does have the correct interpretation, however I can see how my phrasing would indicate the version that MDCCL came to. I apologize for the poor phrasing. I don't currently have a situation where I might need both. I am looking for an example where I might so that I can improve my skillset. And prevent myself from trying to solve all problems one way. The whole idea of "if you are a hammer, everything looks like a nail".
– Kirk Saunders
5 hours ago
add a comment |
I am looking to better build my SQL Development skill set. Where I work currently most all the data fits into a hierarchy and we almost always start with the top most element to work our way "down". This results in really only needing to use INNER and LEFT OUTER joins for the various queries.
I am hoping to get some guidance on a situation where I might need to use a RIGHT OUTER and a LEFT OUTER JOIN in the same query.
Referencing a previous question on StackOverflow What is the difference between Left, Right, Outer and Inner Joins? I understand what a RIGHT OUTER and a LEFT OUTER JOIN is, however all problems I have been asked to solve, could be solved using only INNER and LEFT OUTER joins.
EDIT:
I did find that this is basically a duplicate of a previous StackOverflow question When or why would you use a right outer join instead of left?. It would appear that in general there is a consensus that
- Most all queries using a
RIGHT OUTER JOINcan be converted into an
INNERandLEFT OUTERversion. - There are some benefits for
read-ability formatting or organizational processes which might
require using aRIGHT OUTERinstead of aLEFT OUTER.
There is also an answer on that question which details a particular situation where you would INNER JOIN a group of tables together and then RIGHT OUTER JOIN to find where the "right" table has records in the whole INNER JOIN'ed subset. I think this can be accomplished with an INNER JOIN and LEFT OUTER JOIN version it would just require some work.
sql-server
asked 6 hours ago
Kirk SaundersKirk Saunders
534
I am looking to better build my SQL Development skill set. Where I work currently most all the data fits into a hierarchy and we almost always start with the top most element to work our way "down". This results in really only needing to use INNER and LEFT OUTER joins for the various queries.
I am hoping to get some guidance on a situation where I might need to use a RIGHT OUTER and a LEFT OUTER JOIN in the same query.
Referencing a previous question on StackOverflow What is the difference between Left, Right, Outer and Inner Joins? I understand what a RIGHT OUTER and a LEFT OUTER JOIN is, however all problems I have been asked to solve, could be solved using only INNER and LEFT OUTER joins.
EDIT:
I did find that this is basically a duplicate of a previous StackOverflow question When or why would you use a right outer join instead of left?. It would appear that in general there is a consensus that
- Most all queries using a
RIGHT OUTER JOINcan be converted into an
INNERandLEFT OUTERversion. - There are some benefits for
read-ability formatting or organizational processes which might
require using aRIGHT OUTERinstead of aLEFT OUTER.
There is also an answer on that question which details a particular situation where you would INNER JOIN a group of tables together and then RIGHT OUTER JOIN to find where the "right" table has records in the whole INNER JOIN'ed subset. I think this can be accomplished with an INNER JOIN and LEFT OUTER JOIN version it would just require some work.
sql-server
sql-server
asked 6 hours ago
Kirk SaundersKirk Saunders
534
asked 6 hours ago
Kirk SaundersKirk Saunders
534
edited 5 hours ago
Kirk Saunders
asked 6 hours ago
Kirk SaundersKirk Saunders
534
asked 6 hours ago
Kirk SaundersKirk Saunders
534
asked 6 hours ago
Kirk SaundersKirk Saunders
534
534
You should describe the "situation where I might need to use aRIGHT OUTERand aLEFT OUTER JOINin the same query".
– MDCCL
5 hours ago
@MDCCL That is what the question is. Can we provide guidance on when this would ever be required?
– Martin Smith
5 hours ago
@MartinSmith So that means that the OP wants someone to describe such a situation, that is, provide an example? I'm interpreting the issue as something like "I'm involved in a situation where I might need to use aRIGHT OUTER... in the same query, so I want guidance on it", but it looks like you are understanding the question correctly.
– MDCCL
5 hours ago
@kirk Could you confirm Martin's interpretation?
– MDCCL
5 hours ago
Martin does have the correct interpretation, however I can see how my phrasing would indicate the version that MDCCL came to. I apologize for the poor phrasing. I don't currently have a situation where I might need both. I am looking for an example where I might so that I can improve my skillset. And prevent myself from trying to solve all problems one way. The whole idea of "if you are a hammer, everything looks like a nail".
– Kirk Saunders
5 hours ago
add a comment |
You should describe the "situation where I might need to use aRIGHT OUTERand aLEFT OUTER JOINin the same query".
– MDCCL
5 hours ago
@MDCCL That is what the question is. Can we provide guidance on when this would ever be required?
– Martin Smith
5 hours ago
@MartinSmith So that means that the OP wants someone to describe such a situation, that is, provide an example? I'm interpreting the issue as something like "I'm involved in a situation where I might need to use aRIGHT OUTER... in the same query, so I want guidance on it", but it looks like you are understanding the question correctly.
– MDCCL
5 hours ago
@kirk Could you confirm Martin's interpretation?
– MDCCL
5 hours ago
Martin does have the correct interpretation, however I can see how my phrasing would indicate the version that MDCCL came to. I apologize for the poor phrasing. I don't currently have a situation where I might need both. I am looking for an example where I might so that I can improve my skillset. And prevent myself from trying to solve all problems one way. The whole idea of "if you are a hammer, everything looks like a nail".
– Kirk Saunders
5 hours ago
You should describe the "situation where I might need to use a
RIGHT OUTER and a LEFT OUTER JOIN in the same query".– MDCCL
5 hours ago
You should describe the "situation where I might need to use a
RIGHT OUTER and a LEFT OUTER JOIN in the same query".– MDCCL
5 hours ago
@MDCCL That is what the question is. Can we provide guidance on when this would ever be required?
– Martin Smith
5 hours ago
@MDCCL That is what the question is. Can we provide guidance on when this would ever be required?
– Martin Smith
5 hours ago
@MartinSmith So that means that the OP wants someone to describe such a situation, that is, provide an example? I'm interpreting the issue as something like "I'm involved in a situation where I might need to use a
RIGHT OUTER... in the same query, so I want guidance on it", but it looks like you are understanding the question correctly.– MDCCL
5 hours ago
@MartinSmith So that means that the OP wants someone to describe such a situation, that is, provide an example? I'm interpreting the issue as something like "I'm involved in a situation where I might need to use a
RIGHT OUTER... in the same query, so I want guidance on it", but it looks like you are understanding the question correctly.– MDCCL
5 hours ago
@kirk Could you confirm Martin's interpretation?
– MDCCL
5 hours ago
@kirk Could you confirm Martin's interpretation?
– MDCCL
5 hours ago
Martin does have the correct interpretation, however I can see how my phrasing would indicate the version that MDCCL came to. I apologize for the poor phrasing. I don't currently have a situation where I might need both. I am looking for an example where I might so that I can improve my skillset. And prevent myself from trying to solve all problems one way. The whole idea of "if you are a hammer, everything looks like a nail".
– Kirk Saunders
5 hours ago
Martin does have the correct interpretation, however I can see how my phrasing would indicate the version that MDCCL came to. I apologize for the poor phrasing. I don't currently have a situation where I might need both. I am looking for an example where I might so that I can improve my skillset. And prevent myself from trying to solve all problems one way. The whole idea of "if you are a hammer, everything looks like a nail".
– Kirk Saunders
5 hours ago
add a comment |
1 Answer
1
active
oldest
votes
In practice you never need to use a RIGHT JOIN, and I can't think of a scenario where I would want to.
Any query involving RIGHT JOINs that can't be trivially transformed into a query using only LEFT JOINs should probably be burned with fire.
You can solve almost any problem using a pipeline of CTEs, each of which uses 0 or more INNER JOINs followed by 0 or more LEFT JOINs.
answered 5 hours ago
David Browne - MicrosoftDavid Browne - Microsoft
11.8k729
I hit one once a long time ago; some horrible nasty combination of RIGHT JOIN and NOT IN clauses that was not transformable. I forget exactly what the concoction was. Incidentally, SQL server can't do a right loop join but must hash join the thing.
– Joshua
1 hour ago
1
Not to worry, it's not in production anymore (if it ever was).
– Joshua
59 mins ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230777%2futilizing-a-right-and-left-outer-joins-in-same-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
In practice you never need to use a RIGHT JOIN, and I can't think of a scenario where I would want to.
Any query involving RIGHT JOINs that can't be trivially transformed into a query using only LEFT JOINs should probably be burned with fire.
You can solve almost any problem using a pipeline of CTEs, each of which uses 0 or more INNER JOINs followed by 0 or more LEFT JOINs.
answered 5 hours ago
David Browne - MicrosoftDavid Browne - Microsoft
11.8k729
I hit one once a long time ago; some horrible nasty combination of RIGHT JOIN and NOT IN clauses that was not transformable. I forget exactly what the concoction was. Incidentally, SQL server can't do a right loop join but must hash join the thing.
– Joshua
1 hour ago
1
Not to worry, it's not in production anymore (if it ever was).
– Joshua
59 mins ago
add a comment |
In practice you never need to use a RIGHT JOIN, and I can't think of a scenario where I would want to.
Any query involving RIGHT JOINs that can't be trivially transformed into a query using only LEFT JOINs should probably be burned with fire.
You can solve almost any problem using a pipeline of CTEs, each of which uses 0 or more INNER JOINs followed by 0 or more LEFT JOINs.
answered 5 hours ago
David Browne - MicrosoftDavid Browne - Microsoft
11.8k729
I hit one once a long time ago; some horrible nasty combination of RIGHT JOIN and NOT IN clauses that was not transformable. I forget exactly what the concoction was. Incidentally, SQL server can't do a right loop join but must hash join the thing.
– Joshua
1 hour ago
1
Not to worry, it's not in production anymore (if it ever was).
– Joshua
59 mins ago
add a comment |
In practice you never need to use a RIGHT JOIN, and I can't think of a scenario where I would want to.
Any query involving RIGHT JOINs that can't be trivially transformed into a query using only LEFT JOINs should probably be burned with fire.
You can solve almost any problem using a pipeline of CTEs, each of which uses 0 or more INNER JOINs followed by 0 or more LEFT JOINs.
answered 5 hours ago
David Browne - MicrosoftDavid Browne - Microsoft
11.8k729
In practice you never need to use a RIGHT JOIN, and I can't think of a scenario where I would want to.
Any query involving RIGHT JOINs that can't be trivially transformed into a query using only LEFT JOINs should probably be burned with fire.
You can solve almost any problem using a pipeline of CTEs, each of which uses 0 or more INNER JOINs followed by 0 or more LEFT JOINs.
answered 5 hours ago
David Browne - MicrosoftDavid Browne - Microsoft
11.8k729
edited 5 hours ago
answered 5 hours ago
David Browne - MicrosoftDavid Browne - Microsoft
11.8k729
answered 5 hours ago
David Browne - MicrosoftDavid Browne - Microsoft
11.8k729
answered 5 hours ago
David Browne - MicrosoftDavid Browne - Microsoft
11.8k729
11.8k729
I hit one once a long time ago; some horrible nasty combination of RIGHT JOIN and NOT IN clauses that was not transformable. I forget exactly what the concoction was. Incidentally, SQL server can't do a right loop join but must hash join the thing.
– Joshua
1 hour ago
1
Not to worry, it's not in production anymore (if it ever was).
– Joshua
59 mins ago
add a comment |
I hit one once a long time ago; some horrible nasty combination of RIGHT JOIN and NOT IN clauses that was not transformable. I forget exactly what the concoction was. Incidentally, SQL server can't do a right loop join but must hash join the thing.
– Joshua
1 hour ago
1
Not to worry, it's not in production anymore (if it ever was).
– Joshua
59 mins ago
I hit one once a long time ago; some horrible nasty combination of RIGHT JOIN and NOT IN clauses that was not transformable. I forget exactly what the concoction was. Incidentally, SQL server can't do a right loop join but must hash join the thing.
– Joshua
1 hour ago
I hit one once a long time ago; some horrible nasty combination of RIGHT JOIN and NOT IN clauses that was not transformable. I forget exactly what the concoction was. Incidentally, SQL server can't do a right loop join but must hash join the thing.
– Joshua
1 hour ago
1
1
Not to worry, it's not in production anymore (if it ever was).
– Joshua
59 mins ago
Not to worry, it's not in production anymore (if it ever was).
– Joshua
59 mins ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230777%2futilizing-a-right-and-left-outer-joins-in-same-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You should describe the "situation where I might need to use a
RIGHT OUTERand aLEFT OUTER JOINin the same query".– MDCCL
5 hours ago
@MDCCL That is what the question is. Can we provide guidance on when this would ever be required?
– Martin Smith
5 hours ago
@MartinSmith So that means that the OP wants someone to describe such a situation, that is, provide an example? I'm interpreting the issue as something like "I'm involved in a situation where I might need to use a
RIGHT OUTER... in the same query, so I want guidance on it", but it looks like you are understanding the question correctly.– MDCCL
5 hours ago
@kirk Could you confirm Martin's interpretation?
– MDCCL
5 hours ago
Martin does have the correct interpretation, however I can see how my phrasing would indicate the version that MDCCL came to. I apologize for the poor phrasing. I don't currently have a situation where I might need both. I am looking for an example where I might so that I can improve my skillset. And prevent myself from trying to solve all problems one way. The whole idea of "if you are a hammer, everything looks like a nail".
– Kirk Saunders
5 hours ago