sql query issue step by step
There are 3 relations:
person(id, first_name, original_surname, new_surname, birth_date)marriage(m_id, w_id, date)child_of(child_id, father_id, mother_id)
and the query for half siblings is:
(
select P1.id, P2.id
from (
person join child_of on id=child_id
) as P1
natural join (person join child_of on id=child_id) as P2
using (father_id)
where P1.mother_id < P2.mother_id
)
union
(
select P1.id, P2.id
from (
person join child_of on id=child_id
) as P1
natural join (person join child_of on id=child_id) as P2
using (mother_id)
where P1.father_id < P2.father_id
)
I am new to sql and I am trying to understand what happens "behind the scenes". Which table is created in the end and how the query finds half siblings.
Is P1 is created and then it joins with P2 and then they both join together and how does the where affect on the table?
What is the chronological order of things here, what happens first, second...
sql postgresql
edited Nov 15 '18 at 17:58
a_horse_with_no_name
303k46463560
asked Nov 15 '18 at 17:52
ArthurArthur
286
add a comment |
There are 3 relations:
person(id, first_name, original_surname, new_surname, birth_date)marriage(m_id, w_id, date)child_of(child_id, father_id, mother_id)
and the query for half siblings is:
(
select P1.id, P2.id
from (
person join child_of on id=child_id
) as P1
natural join (person join child_of on id=child_id) as P2
using (father_id)
where P1.mother_id < P2.mother_id
)
union
(
select P1.id, P2.id
from (
person join child_of on id=child_id
) as P1
natural join (person join child_of on id=child_id) as P2
using (mother_id)
where P1.father_id < P2.father_id
)
I am new to sql and I am trying to understand what happens "behind the scenes". Which table is created in the end and how the query finds half siblings.
Is P1 is created and then it joins with P2 and then they both join together and how does the where affect on the table?
What is the chronological order of things here, what happens first, second...
sql postgresql
edited Nov 15 '18 at 17:58
a_horse_with_no_name
303k46463560
asked Nov 15 '18 at 17:52
ArthurArthur
286
Your code does not create any tables. Are you asking how the result set is being built?
– PM 77-1
Nov 15 '18 at 17:57
In this casewhereserves as a join condition.
– PM 77-1
Nov 15 '18 at 18:02
What's will all those brackets???
– Eric
Nov 15 '18 at 18:06
@PM77-1 yes. I mean the tables that are there "behind the scenes"
– Arthur
Nov 15 '18 at 18:32
add a comment |
There are 3 relations:
person(id, first_name, original_surname, new_surname, birth_date)marriage(m_id, w_id, date)child_of(child_id, father_id, mother_id)
and the query for half siblings is:
(
select P1.id, P2.id
from (
person join child_of on id=child_id
) as P1
natural join (person join child_of on id=child_id) as P2
using (father_id)
where P1.mother_id < P2.mother_id
)
union
(
select P1.id, P2.id
from (
person join child_of on id=child_id
) as P1
natural join (person join child_of on id=child_id) as P2
using (mother_id)
where P1.father_id < P2.father_id
)
I am new to sql and I am trying to understand what happens "behind the scenes". Which table is created in the end and how the query finds half siblings.
Is P1 is created and then it joins with P2 and then they both join together and how does the where affect on the table?
What is the chronological order of things here, what happens first, second...
sql postgresql
edited Nov 15 '18 at 17:58
a_horse_with_no_name
303k46463560
asked Nov 15 '18 at 17:52
ArthurArthur
286
There are 3 relations:
person(id, first_name, original_surname, new_surname, birth_date)marriage(m_id, w_id, date)child_of(child_id, father_id, mother_id)
and the query for half siblings is:
(
select P1.id, P2.id
from (
person join child_of on id=child_id
) as P1
natural join (person join child_of on id=child_id) as P2
using (father_id)
where P1.mother_id < P2.mother_id
)
union
(
select P1.id, P2.id
from (
person join child_of on id=child_id
) as P1
natural join (person join child_of on id=child_id) as P2
using (mother_id)
where P1.father_id < P2.father_id
)
I am new to sql and I am trying to understand what happens "behind the scenes". Which table is created in the end and how the query finds half siblings.
Is P1 is created and then it joins with P2 and then they both join together and how does the where affect on the table?
What is the chronological order of things here, what happens first, second...
sql postgresql
sql postgresql
edited Nov 15 '18 at 17:58
a_horse_with_no_name
303k46463560
asked Nov 15 '18 at 17:52
ArthurArthur
286
edited Nov 15 '18 at 17:58
a_horse_with_no_name
303k46463560
asked Nov 15 '18 at 17:52
ArthurArthur
286
edited Nov 15 '18 at 17:58
a_horse_with_no_name
303k46463560
edited Nov 15 '18 at 17:58
a_horse_with_no_name
303k46463560
edited Nov 15 '18 at 17:58
a_horse_with_no_name
303k46463560
303k46463560
asked Nov 15 '18 at 17:52
ArthurArthur
286
asked Nov 15 '18 at 17:52
ArthurArthur
286
asked Nov 15 '18 at 17:52
ArthurArthur
286
286
Your code does not create any tables. Are you asking how the result set is being built?
– PM 77-1
Nov 15 '18 at 17:57
In this casewhereserves as a join condition.
– PM 77-1
Nov 15 '18 at 18:02
What's will all those brackets???
– Eric
Nov 15 '18 at 18:06
@PM77-1 yes. I mean the tables that are there "behind the scenes"
– Arthur
Nov 15 '18 at 18:32
add a comment |
Your code does not create any tables. Are you asking how the result set is being built?
– PM 77-1
Nov 15 '18 at 17:57
In this casewhereserves as a join condition.
– PM 77-1
Nov 15 '18 at 18:02
What's will all those brackets???
– Eric
Nov 15 '18 at 18:06
@PM77-1 yes. I mean the tables that are there "behind the scenes"
– Arthur
Nov 15 '18 at 18:32
Your code does not create any tables. Are you asking how the result set is being built?
– PM 77-1
Nov 15 '18 at 17:57
Your code does not create any tables. Are you asking how the result set is being built?
– PM 77-1
Nov 15 '18 at 17:57
In this case
where serves as a join condition.– PM 77-1
Nov 15 '18 at 18:02
In this case
where serves as a join condition.– PM 77-1
Nov 15 '18 at 18:02
What's will all those brackets???
– Eric
Nov 15 '18 at 18:06
What's will all those brackets???
– Eric
Nov 15 '18 at 18:06
@PM77-1 yes. I mean the tables that are there "behind the scenes"
– Arthur
Nov 15 '18 at 18:32
@PM77-1 yes. I mean the tables that are there "behind the scenes"
– Arthur
Nov 15 '18 at 18:32
add a comment |
1 Answer
1
active
oldest
votes
Processing here won't go beyond the parser step, which will detect a syntax error in the query. Natural joins (which are best avoided) don't sport a USING clause.
But assuming that you mean INNER instead of NATURAL, the answer depends a lot on the amount and distribution of the data in the tables, as well as the configuration of the database.
Also, indexes on the tables have an influence on the answer.
There are many different ways to execute such a query, and the database engine will automatically select the one it thinks is fastest under the given circumstances.
You can use the EXPLAIN command to see which execution plan PostgreSQL chooses.
answered Nov 15 '18 at 18:04
Laurenz AlbeLaurenz Albe
50.2k102950
Short but very good answer. Or better: short AND very good answer. :-)
– johey
Nov 15 '18 at 18:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53325292%2fsql-query-issue-step-by-step%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
Processing here won't go beyond the parser step, which will detect a syntax error in the query. Natural joins (which are best avoided) don't sport a USING clause.
But assuming that you mean INNER instead of NATURAL, the answer depends a lot on the amount and distribution of the data in the tables, as well as the configuration of the database.
Also, indexes on the tables have an influence on the answer.
There are many different ways to execute such a query, and the database engine will automatically select the one it thinks is fastest under the given circumstances.
You can use the EXPLAIN command to see which execution plan PostgreSQL chooses.
answered Nov 15 '18 at 18:04
Laurenz AlbeLaurenz Albe
50.2k102950
Short but very good answer. Or better: short AND very good answer. :-)
– johey
Nov 15 '18 at 18:09
add a comment |
Processing here won't go beyond the parser step, which will detect a syntax error in the query. Natural joins (which are best avoided) don't sport a USING clause.
But assuming that you mean INNER instead of NATURAL, the answer depends a lot on the amount and distribution of the data in the tables, as well as the configuration of the database.
Also, indexes on the tables have an influence on the answer.
There are many different ways to execute such a query, and the database engine will automatically select the one it thinks is fastest under the given circumstances.
You can use the EXPLAIN command to see which execution plan PostgreSQL chooses.
answered Nov 15 '18 at 18:04
Laurenz AlbeLaurenz Albe
50.2k102950
Short but very good answer. Or better: short AND very good answer. :-)
– johey
Nov 15 '18 at 18:09
add a comment |
Processing here won't go beyond the parser step, which will detect a syntax error in the query. Natural joins (which are best avoided) don't sport a USING clause.
But assuming that you mean INNER instead of NATURAL, the answer depends a lot on the amount and distribution of the data in the tables, as well as the configuration of the database.
Also, indexes on the tables have an influence on the answer.
There are many different ways to execute such a query, and the database engine will automatically select the one it thinks is fastest under the given circumstances.
You can use the EXPLAIN command to see which execution plan PostgreSQL chooses.
answered Nov 15 '18 at 18:04
Laurenz AlbeLaurenz Albe
50.2k102950
Processing here won't go beyond the parser step, which will detect a syntax error in the query. Natural joins (which are best avoided) don't sport a USING clause.
But assuming that you mean INNER instead of NATURAL, the answer depends a lot on the amount and distribution of the data in the tables, as well as the configuration of the database.
Also, indexes on the tables have an influence on the answer.
There are many different ways to execute such a query, and the database engine will automatically select the one it thinks is fastest under the given circumstances.
You can use the EXPLAIN command to see which execution plan PostgreSQL chooses.
answered Nov 15 '18 at 18:04
Laurenz AlbeLaurenz Albe
50.2k102950
answered Nov 15 '18 at 18:04
Laurenz AlbeLaurenz Albe
50.2k102950
answered Nov 15 '18 at 18:04
Laurenz AlbeLaurenz Albe
50.2k102950
answered Nov 15 '18 at 18:04
Laurenz AlbeLaurenz Albe
50.2k102950
50.2k102950
Short but very good answer. Or better: short AND very good answer. :-)
– johey
Nov 15 '18 at 18:09
add a comment |
Short but very good answer. Or better: short AND very good answer. :-)
– johey
Nov 15 '18 at 18:09
Short but very good answer. Or better: short AND very good answer. :-)
– johey
Nov 15 '18 at 18:09
Short but very good answer. Or better: short AND very good answer. :-)
– johey
Nov 15 '18 at 18:09
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53325292%2fsql-query-issue-step-by-step%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
Your code does not create any tables. Are you asking how the result set is being built?
– PM 77-1
Nov 15 '18 at 17:57
In this case
whereserves as a join condition.– PM 77-1
Nov 15 '18 at 18:02
What's will all those brackets???
– Eric
Nov 15 '18 at 18:06
@PM77-1 yes. I mean the tables that are there "behind the scenes"
– Arthur
Nov 15 '18 at 18:32