What is this WHERE clause adding to the query?
0
I am trying to better understand the answer to the following query: https://www.w3resource.com/sql-exercises/sql-subqueries-exercise-22.php
The answer is:
SELECT
departments.department_id, result1.total_amt
FROM
departments,
(SELECT employees.department_id, SUM(employees.salary) total_amt
FROM employees
GROUP BY department_id) result1
WHERE
result1.department_id = departments.department_id;
However, I do not understand why I need the last part:
WHERE result1.department_id = departments.department_id
sql
edited Nov 26 '18 at 19:19
marc_s
577k12911141259
asked Nov 26 '18 at 18:41
Justin ChitlaJustin Chitla
11114
add a comment |
0
I am trying to better understand the answer to the following query: https://www.w3resource.com/sql-exercises/sql-subqueries-exercise-22.php
The answer is:
SELECT
departments.department_id, result1.total_amt
FROM
departments,
(SELECT employees.department_id, SUM(employees.salary) total_amt
FROM employees
GROUP BY department_id) result1
WHERE
result1.department_id = departments.department_id;
However, I do not understand why I need the last part:
WHERE result1.department_id = departments.department_id
sql
edited Nov 26 '18 at 19:19
marc_s
577k12911141259
asked Nov 26 '18 at 18:41
Justin ChitlaJustin Chitla
11114
10
You need it because you did not use proper, explicit, standardJOINsyntax.
– Gordon Linoff
Nov 26 '18 at 18:42
4
It's an old, outdated way of specifying a join condition.
– a_horse_with_no_name
Nov 26 '18 at 18:45
Try removing it and see what happens.
– Bob Jarvis
Nov 26 '18 at 19:22
1
When you include more than one table in your SQL you must specify upon which columns the tables are related to one another. In this casedepartments.department_idis related to the derived table'sresult1.department_id. Generally we specify that with theONclause in aJOIN, but you can also list the tables in theFROMcomma separated like this and then specify the relationship in theWHEREclause. It's frowned upon though. If you fail to specify the relationship in anONorWHEREthen you will end up with every row in one table being joined to every other row in the other.
– JNevill
Nov 26 '18 at 19:24
add a comment |
0
0
0
I am trying to better understand the answer to the following query: https://www.w3resource.com/sql-exercises/sql-subqueries-exercise-22.php
The answer is:
SELECT
departments.department_id, result1.total_amt
FROM
departments,
(SELECT employees.department_id, SUM(employees.salary) total_amt
FROM employees
GROUP BY department_id) result1
WHERE
result1.department_id = departments.department_id;
However, I do not understand why I need the last part:
WHERE result1.department_id = departments.department_id
sql
edited Nov 26 '18 at 19:19
marc_s
577k12911141259
asked Nov 26 '18 at 18:41
Justin ChitlaJustin Chitla
11114
I am trying to better understand the answer to the following query: https://www.w3resource.com/sql-exercises/sql-subqueries-exercise-22.php
The answer is:
SELECT
departments.department_id, result1.total_amt
FROM
departments,
(SELECT employees.department_id, SUM(employees.salary) total_amt
FROM employees
GROUP BY department_id) result1
WHERE
result1.department_id = departments.department_id;
However, I do not understand why I need the last part:
WHERE result1.department_id = departments.department_id
sql
sql
edited Nov 26 '18 at 19:19
marc_s
577k12911141259
asked Nov 26 '18 at 18:41
Justin ChitlaJustin Chitla
11114
edited Nov 26 '18 at 19:19
marc_s
577k12911141259
asked Nov 26 '18 at 18:41
Justin ChitlaJustin Chitla
11114
edited Nov 26 '18 at 19:19
marc_s
577k12911141259
edited Nov 26 '18 at 19:19
marc_s
577k12911141259
edited Nov 26 '18 at 19:19
marc_s
577k12911141259
577k12911141259
asked Nov 26 '18 at 18:41
Justin ChitlaJustin Chitla
11114
asked Nov 26 '18 at 18:41
Justin ChitlaJustin Chitla
11114
asked Nov 26 '18 at 18:41
Justin ChitlaJustin Chitla
11114
11114
10
You need it because you did not use proper, explicit, standardJOINsyntax.
– Gordon Linoff
Nov 26 '18 at 18:42
4
It's an old, outdated way of specifying a join condition.
– a_horse_with_no_name
Nov 26 '18 at 18:45
Try removing it and see what happens.
– Bob Jarvis
Nov 26 '18 at 19:22
1
When you include more than one table in your SQL you must specify upon which columns the tables are related to one another. In this casedepartments.department_idis related to the derived table'sresult1.department_id. Generally we specify that with theONclause in aJOIN, but you can also list the tables in theFROMcomma separated like this and then specify the relationship in theWHEREclause. It's frowned upon though. If you fail to specify the relationship in anONorWHEREthen you will end up with every row in one table being joined to every other row in the other.
– JNevill
Nov 26 '18 at 19:24
add a comment |
10
You need it because you did not use proper, explicit, standardJOINsyntax.
– Gordon Linoff
Nov 26 '18 at 18:42
4
It's an old, outdated way of specifying a join condition.
– a_horse_with_no_name
Nov 26 '18 at 18:45
Try removing it and see what happens.
– Bob Jarvis
Nov 26 '18 at 19:22
1
When you include more than one table in your SQL you must specify upon which columns the tables are related to one another. In this casedepartments.department_idis related to the derived table'sresult1.department_id. Generally we specify that with theONclause in aJOIN, but you can also list the tables in theFROMcomma separated like this and then specify the relationship in theWHEREclause. It's frowned upon though. If you fail to specify the relationship in anONorWHEREthen you will end up with every row in one table being joined to every other row in the other.
– JNevill
Nov 26 '18 at 19:24
10
10
You need it because you did not use proper, explicit, standard
JOIN syntax.– Gordon Linoff
Nov 26 '18 at 18:42
You need it because you did not use proper, explicit, standard
JOIN syntax.– Gordon Linoff
Nov 26 '18 at 18:42
4
4
It's an old, outdated way of specifying a join condition.
– a_horse_with_no_name
Nov 26 '18 at 18:45
It's an old, outdated way of specifying a join condition.
– a_horse_with_no_name
Nov 26 '18 at 18:45
Try removing it and see what happens.
– Bob Jarvis
Nov 26 '18 at 19:22
Try removing it and see what happens.
– Bob Jarvis
Nov 26 '18 at 19:22
1
1
When you include more than one table in your SQL you must specify upon which columns the tables are related to one another. In this case
departments.department_id is related to the derived table'sresult1.department_id. Generally we specify that with the ON clause in a JOIN, but you can also list the tables in the FROM comma separated like this and then specify the relationship in the WHERE clause. It's frowned upon though. If you fail to specify the relationship in an ON or WHERE then you will end up with every row in one table being joined to every other row in the other.– JNevill
Nov 26 '18 at 19:24
When you include more than one table in your SQL you must specify upon which columns the tables are related to one another. In this case
departments.department_id is related to the derived table'sresult1.department_id. Generally we specify that with the ON clause in a JOIN, but you can also list the tables in the FROM comma separated like this and then specify the relationship in the WHERE clause. It's frowned upon though. If you fail to specify the relationship in an ON or WHERE then you will end up with every row in one table being joined to every other row in the other.– JNevill
Nov 26 '18 at 19:24
add a comment |
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53487180%2fwhat-is-this-where-clause-adding-to-the-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53487180%2fwhat-is-this-where-clause-adding-to-the-query%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
10
You need it because you did not use proper, explicit, standard
JOINsyntax.– Gordon Linoff
Nov 26 '18 at 18:42
4
It's an old, outdated way of specifying a join condition.
– a_horse_with_no_name
Nov 26 '18 at 18:45
Try removing it and see what happens.
– Bob Jarvis
Nov 26 '18 at 19:22
1
When you include more than one table in your SQL you must specify upon which columns the tables are related to one another. In this case
departments.department_idis related to the derived table'sresult1.department_id. Generally we specify that with theONclause in aJOIN, but you can also list the tables in theFROMcomma separated like this and then specify the relationship in theWHEREclause. It's frowned upon though. If you fail to specify the relationship in anONorWHEREthen you will end up with every row in one table being joined to every other row in the other.– JNevill
Nov 26 '18 at 19:24