Why Toad turned my LEFT OUTER JOIN into a RIGHT OUTER JOIN
I finally figured out the answer but it might be useful for someone looking up the same question so here is the whole write up to hopefully save you the frustration.
Problem I had:
Toad's query builder has 3 tabs - Diagram, Query and Results.
I adjusted my query to what I wanted in Query tab. Ran it and confirmed that Results tab looked good. Then, I wanted to sync Query to Diagram. After syncing I noticed that my LEFT OUTER JOIN was turned into a RIGHT OUTER JOIN.
Why? Won't the results be wrong?
I wanted all records from INVOICES table and only matching records from INVOICE_ITEMS table.
My original query's FROM statement was:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)
And Toad kept turning it into:
FROM INVOICES
RIGHT OUTER JOIN INVOICE_ITEMS
ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)
It was driving me crazy. Why was it doing that?
Well, the solution was simple. I apparently reversed my tables and Toad was smart enough to fix it for me. (Facepalm) I'm still not sure if it's a good feature or not considering my confusion but it's definitely there.
So, the correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
oracle join toad
asked Apr 11 '18 at 19:17
KamiccolaKamiccola
1
bumped to the homepage by Community♦ 12 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I finally figured out the answer but it might be useful for someone looking up the same question so here is the whole write up to hopefully save you the frustration.
Problem I had:
Toad's query builder has 3 tabs - Diagram, Query and Results.
I adjusted my query to what I wanted in Query tab. Ran it and confirmed that Results tab looked good. Then, I wanted to sync Query to Diagram. After syncing I noticed that my LEFT OUTER JOIN was turned into a RIGHT OUTER JOIN.
Why? Won't the results be wrong?
I wanted all records from INVOICES table and only matching records from INVOICE_ITEMS table.
My original query's FROM statement was:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)
And Toad kept turning it into:
FROM INVOICES
RIGHT OUTER JOIN INVOICE_ITEMS
ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)
It was driving me crazy. Why was it doing that?
Well, the solution was simple. I apparently reversed my tables and Toad was smart enough to fix it for me. (Facepalm) I'm still not sure if it's a good feature or not considering my confusion but it's definitely there.
So, the correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
oracle join toad
asked Apr 11 '18 at 19:17
KamiccolaKamiccola
1
bumped to the homepage by Community♦ 12 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Do both tables have two columns,INVOICE_GKEYandGKEY? Which one is the PK in each table?
– ypercubeᵀᴹ
Apr 11 '18 at 19:24
@ypercube GKEY is the PK for INVOICES table. INVOICE_GKEY is FK in INVOICE_ITMES table. I don't know why all keys are called GKEYs in this database.
– Kamiccola
Apr 20 '18 at 19:38
@joeW but I did. "So, the correct syntax..." Am I missing something?
– Kamiccola
Apr 20 '18 at 19:39
Post it as an answer so people can see that the question has an answer and more easily find it.
– Joe W
Apr 20 '18 at 19:52
add a comment |
I finally figured out the answer but it might be useful for someone looking up the same question so here is the whole write up to hopefully save you the frustration.
Problem I had:
Toad's query builder has 3 tabs - Diagram, Query and Results.
I adjusted my query to what I wanted in Query tab. Ran it and confirmed that Results tab looked good. Then, I wanted to sync Query to Diagram. After syncing I noticed that my LEFT OUTER JOIN was turned into a RIGHT OUTER JOIN.
Why? Won't the results be wrong?
I wanted all records from INVOICES table and only matching records from INVOICE_ITEMS table.
My original query's FROM statement was:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)
And Toad kept turning it into:
FROM INVOICES
RIGHT OUTER JOIN INVOICE_ITEMS
ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)
It was driving me crazy. Why was it doing that?
Well, the solution was simple. I apparently reversed my tables and Toad was smart enough to fix it for me. (Facepalm) I'm still not sure if it's a good feature or not considering my confusion but it's definitely there.
So, the correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
oracle join toad
asked Apr 11 '18 at 19:17
KamiccolaKamiccola
1
I finally figured out the answer but it might be useful for someone looking up the same question so here is the whole write up to hopefully save you the frustration.
Problem I had:
Toad's query builder has 3 tabs - Diagram, Query and Results.
I adjusted my query to what I wanted in Query tab. Ran it and confirmed that Results tab looked good. Then, I wanted to sync Query to Diagram. After syncing I noticed that my LEFT OUTER JOIN was turned into a RIGHT OUTER JOIN.
Why? Won't the results be wrong?
I wanted all records from INVOICES table and only matching records from INVOICE_ITEMS table.
My original query's FROM statement was:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)
And Toad kept turning it into:
FROM INVOICES
RIGHT OUTER JOIN INVOICE_ITEMS
ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY)
It was driving me crazy. Why was it doing that?
Well, the solution was simple. I apparently reversed my tables and Toad was smart enough to fix it for me. (Facepalm) I'm still not sure if it's a good feature or not considering my confusion but it's definitely there.
So, the correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
oracle join toad
oracle join toad
asked Apr 11 '18 at 19:17
KamiccolaKamiccola
1
asked Apr 11 '18 at 19:17
KamiccolaKamiccola
1
asked Apr 11 '18 at 19:17
KamiccolaKamiccola
1
asked Apr 11 '18 at 19:17
KamiccolaKamiccola
1
asked Apr 11 '18 at 19:17
KamiccolaKamiccola
1
1
bumped to the homepage by Community♦ 12 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 12 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Do both tables have two columns,INVOICE_GKEYandGKEY? Which one is the PK in each table?
– ypercubeᵀᴹ
Apr 11 '18 at 19:24
@ypercube GKEY is the PK for INVOICES table. INVOICE_GKEY is FK in INVOICE_ITMES table. I don't know why all keys are called GKEYs in this database.
– Kamiccola
Apr 20 '18 at 19:38
@joeW but I did. "So, the correct syntax..." Am I missing something?
– Kamiccola
Apr 20 '18 at 19:39
Post it as an answer so people can see that the question has an answer and more easily find it.
– Joe W
Apr 20 '18 at 19:52
add a comment |
Do both tables have two columns,INVOICE_GKEYandGKEY? Which one is the PK in each table?
– ypercubeᵀᴹ
Apr 11 '18 at 19:24
@ypercube GKEY is the PK for INVOICES table. INVOICE_GKEY is FK in INVOICE_ITMES table. I don't know why all keys are called GKEYs in this database.
– Kamiccola
Apr 20 '18 at 19:38
@joeW but I did. "So, the correct syntax..." Am I missing something?
– Kamiccola
Apr 20 '18 at 19:39
Post it as an answer so people can see that the question has an answer and more easily find it.
– Joe W
Apr 20 '18 at 19:52
Do both tables have two columns,
INVOICE_GKEY and GKEY? Which one is the PK in each table?– ypercubeᵀᴹ
Apr 11 '18 at 19:24
Do both tables have two columns,
INVOICE_GKEY and GKEY? Which one is the PK in each table?– ypercubeᵀᴹ
Apr 11 '18 at 19:24
@ypercube GKEY is the PK for INVOICES table. INVOICE_GKEY is FK in INVOICE_ITMES table. I don't know why all keys are called GKEYs in this database.
– Kamiccola
Apr 20 '18 at 19:38
@ypercube GKEY is the PK for INVOICES table. INVOICE_GKEY is FK in INVOICE_ITMES table. I don't know why all keys are called GKEYs in this database.
– Kamiccola
Apr 20 '18 at 19:38
@joeW but I did. "So, the correct syntax..." Am I missing something?
– Kamiccola
Apr 20 '18 at 19:39
@joeW but I did. "So, the correct syntax..." Am I missing something?
– Kamiccola
Apr 20 '18 at 19:39
Post it as an answer so people can see that the question has an answer and more easily find it.
– Joe W
Apr 20 '18 at 19:52
Post it as an answer so people can see that the question has an answer and more easily find it.
– Joe W
Apr 20 '18 at 19:52
add a comment |
1 Answer
1
active
oldest
votes
The correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
the table on the left of my statement is the one I want all records of and the table on the right is the one I want only matching records.
answered Apr 27 '18 at 14:57
KamiccolaKamiccola
1
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%2f203678%2fwhy-toad-turned-my-left-outer-join-into-a-right-outer-join%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
The correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
the table on the left of my statement is the one I want all records of and the table on the right is the one I want only matching records.
answered Apr 27 '18 at 14:57
KamiccolaKamiccola
1
add a comment |
The correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
the table on the left of my statement is the one I want all records of and the table on the right is the one I want only matching records.
answered Apr 27 '18 at 14:57
KamiccolaKamiccola
1
add a comment |
The correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
the table on the left of my statement is the one I want all records of and the table on the right is the one I want only matching records.
answered Apr 27 '18 at 14:57
KamiccolaKamiccola
1
The correct syntax for LEFT OUTER JOIN was supposed to be:
FROM INVOICES
LEFT OUTER JOIN INVOICE_ITEMS
ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)
the table on the left of my statement is the one I want all records of and the table on the right is the one I want only matching records.
answered Apr 27 '18 at 14:57
KamiccolaKamiccola
1
answered Apr 27 '18 at 14:57
KamiccolaKamiccola
1
answered Apr 27 '18 at 14:57
KamiccolaKamiccola
1
answered Apr 27 '18 at 14:57
KamiccolaKamiccola
1
1
add a comment |
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%2f203678%2fwhy-toad-turned-my-left-outer-join-into-a-right-outer-join%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
Do both tables have two columns,
INVOICE_GKEYandGKEY? Which one is the PK in each table?– ypercubeᵀᴹ
Apr 11 '18 at 19:24
@ypercube GKEY is the PK for INVOICES table. INVOICE_GKEY is FK in INVOICE_ITMES table. I don't know why all keys are called GKEYs in this database.
– Kamiccola
Apr 20 '18 at 19:38
@joeW but I did. "So, the correct syntax..." Am I missing something?
– Kamiccola
Apr 20 '18 at 19:39
Post it as an answer so people can see that the question has an answer and more easily find it.
– Joe W
Apr 20 '18 at 19:52