Why is LEFT JOIN and INNER JOIN returning exact results?
2
Somebody has to know why this happens...
The first two scripts are almost exactly the same, the only difference is that one is a left join and the other one is an inner join. But they both return the same exact rows:
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c inner join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
The same script with a left join, returns the same number of rows:
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c left join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
The next, and 3rd, script, correctly displays the left join. The only difference between this script and the previous one is that I commented the inserts so that @whatjoins only has 'ccc'.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
--select 'aaa', 1, 400 union
--select 'aaa', 2, 400 union
--select 'aaa', 3, 400 union
--select 'aaa', 4, 400 union
--select 'bbb', 1, 500 union
--select 'bbb', 3, 500 union
--select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c left join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
My main question: why are the first 2 scripts returning the same data?
My secondary question: is there some similar way to use the left join that will return all 12 rows (10 matches and 2 rows with null)?
This is the result I'm looking for:
1 bbb 1 500
2 NULL NULL NULL
3 bbb 3 500
4 bbb 4 999
1 ccc 1 999
2 ccc 2 999
3 NULL NULL NULL
4 ccc 4 999
1 aaa 1 400
2 aaa 2 400
3 aaa 3 400
4 aaa 4 400
sql
sql-server tsql sql-server-2008 asked Nov 13 '18 at 13:36
fdkgfosfskjdlsjdlkfsffdkgfosfskjdlsjdlkfsf
1,147937
add a comment |
2
Somebody has to know why this happens...
The first two scripts are almost exactly the same, the only difference is that one is a left join and the other one is an inner join. But they both return the same exact rows:
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c inner join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
The same script with a left join, returns the same number of rows:
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c left join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
The next, and 3rd, script, correctly displays the left join. The only difference between this script and the previous one is that I commented the inserts so that @whatjoins only has 'ccc'.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
--select 'aaa', 1, 400 union
--select 'aaa', 2, 400 union
--select 'aaa', 3, 400 union
--select 'aaa', 4, 400 union
--select 'bbb', 1, 500 union
--select 'bbb', 3, 500 union
--select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c left join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
My main question: why are the first 2 scripts returning the same data?
My secondary question: is there some similar way to use the left join that will return all 12 rows (10 matches and 2 rows with null)?
This is the result I'm looking for:
1 bbb 1 500
2 NULL NULL NULL
3 bbb 3 500
4 bbb 4 999
1 ccc 1 999
2 ccc 2 999
3 NULL NULL NULL
4 ccc 4 999
1 aaa 1 400
2 aaa 2 400
3 aaa 3 400
4 aaa 4 400
sql
sql-server tsql sql-server-2008 asked Nov 13 '18 at 13:36
fdkgfosfskjdlsjdlkfsffdkgfosfskjdlsjdlkfsf
1,147937
What is the criteria for getting NULL in your expected result? Left join is working as expected.
– WhoamI
Nov 13 '18 at 14:18
The second query is an inner join. It returns the 10 rows that match with both tables. If it were a left join it would've ideally returned all 12 rows.
– fdkgfosfskjdlsjdlkfsf
Nov 13 '18 at 14:28
add a comment |
2
2
2
Somebody has to know why this happens...
The first two scripts are almost exactly the same, the only difference is that one is a left join and the other one is an inner join. But they both return the same exact rows:
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c inner join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
The same script with a left join, returns the same number of rows:
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c left join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
The next, and 3rd, script, correctly displays the left join. The only difference between this script and the previous one is that I commented the inserts so that @whatjoins only has 'ccc'.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
--select 'aaa', 1, 400 union
--select 'aaa', 2, 400 union
--select 'aaa', 3, 400 union
--select 'aaa', 4, 400 union
--select 'bbb', 1, 500 union
--select 'bbb', 3, 500 union
--select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c left join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
My main question: why are the first 2 scripts returning the same data?
My secondary question: is there some similar way to use the left join that will return all 12 rows (10 matches and 2 rows with null)?
This is the result I'm looking for:
1 bbb 1 500
2 NULL NULL NULL
3 bbb 3 500
4 bbb 4 999
1 ccc 1 999
2 ccc 2 999
3 NULL NULL NULL
4 ccc 4 999
1 aaa 1 400
2 aaa 2 400
3 aaa 3 400
4 aaa 4 400
sql
sql-server tsql sql-server-2008 asked Nov 13 '18 at 13:36
fdkgfosfskjdlsjdlkfsffdkgfosfskjdlsjdlkfsf
1,147937
Somebody has to know why this happens...
The first two scripts are almost exactly the same, the only difference is that one is a left join and the other one is an inner join. But they both return the same exact rows:
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c inner join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
The same script with a left join, returns the same number of rows:
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c left join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
The next, and 3rd, script, correctly displays the left join. The only difference between this script and the previous one is that I commented the inserts so that @whatjoins only has 'ccc'.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
--select 'aaa', 1, 400 union
--select 'aaa', 2, 400 union
--select 'aaa', 3, 400 union
--select 'aaa', 4, 400 union
--select 'bbb', 1, 500 union
--select 'bbb', 3, 500 union
--select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthnum, w.id, w.monthnum, w.total
from @calendar c left join @whatjoins w on
c.monthNum = w.monthnum
order by c.monthNum, w.monthnum, id
My main question: why are the first 2 scripts returning the same data?
My secondary question: is there some similar way to use the left join that will return all 12 rows (10 matches and 2 rows with null)?
This is the result I'm looking for:
1 bbb 1 500
2 NULL NULL NULL
3 bbb 3 500
4 bbb 4 999
1 ccc 1 999
2 ccc 2 999
3 NULL NULL NULL
4 ccc 4 999
1 aaa 1 400
2 aaa 2 400
3 aaa 3 400
4 aaa 4 400
sql
sql-server tsql sql-server-2008 sql
sql-server tsql sql-server-2008 asked Nov 13 '18 at 13:36
fdkgfosfskjdlsjdlkfsffdkgfosfskjdlsjdlkfsf
1,147937
asked Nov 13 '18 at 13:36
fdkgfosfskjdlsjdlkfsffdkgfosfskjdlsjdlkfsf
1,147937
edited Nov 20 '18 at 19:27
fdkgfosfskjdlsjdlkfsf
asked Nov 13 '18 at 13:36
fdkgfosfskjdlsjdlkfsffdkgfosfskjdlsjdlkfsf
1,147937
asked Nov 13 '18 at 13:36
fdkgfosfskjdlsjdlkfsffdkgfosfskjdlsjdlkfsf
1,147937
asked Nov 13 '18 at 13:36
fdkgfosfskjdlsjdlkfsffdkgfosfskjdlsjdlkfsf
1,147937
1,147937
What is the criteria for getting NULL in your expected result? Left join is working as expected.
– WhoamI
Nov 13 '18 at 14:18
The second query is an inner join. It returns the 10 rows that match with both tables. If it were a left join it would've ideally returned all 12 rows.
– fdkgfosfskjdlsjdlkfsf
Nov 13 '18 at 14:28
add a comment |
What is the criteria for getting NULL in your expected result? Left join is working as expected.
– WhoamI
Nov 13 '18 at 14:18
The second query is an inner join. It returns the 10 rows that match with both tables. If it were a left join it would've ideally returned all 12 rows.
– fdkgfosfskjdlsjdlkfsf
Nov 13 '18 at 14:28
What is the criteria for getting NULL in your expected result? Left join is working as expected.
– WhoamI
Nov 13 '18 at 14:18
What is the criteria for getting NULL in your expected result? Left join is working as expected.
– WhoamI
Nov 13 '18 at 14:18
The second query is an inner join. It returns the 10 rows that match with both tables. If it were a left join it would've ideally returned all 12 rows.
– fdkgfosfskjdlsjdlkfsf
Nov 13 '18 at 14:28
The second query is an inner join. It returns the 10 rows that match with both tables. If it were a left join it would've ideally returned all 12 rows.
– fdkgfosfskjdlsjdlkfsf
Nov 13 '18 at 14:28
add a comment |
2 Answers
2
active
oldest
votes
0
Not an efficient one, but works.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthNum,w2.*
from @calendar c CROSS JOIN (SELECT DISTINCT id FROM @whatjoins w)w
LEFT JOIN @whatjoins w2 ON w2.id = w.id AND c.monthNum=w2.monthnum
ORDER BY id
Only thing is I can't figure out an order by for this for the exact output you asked
ORDER BY ROW_NUMBER() OVER(PARTITION BY c.monthNum ORDER BY c.monthNum)
this is something I have come across but still not valid
answered Nov 13 '18 at 14:11
Biju joseBiju jose
19719
add a comment |
0
You need CROSS JOIN before LEFT JOIN because of you want to repeat all monthnums for each ids :
select distinct c.monthNum, w.id, w1.monthnum, w1.total
from @whatjoins w cross join
@calendar c left join
@whatjoins w1
on w1.id = w.id and w1.monthnum = c.monthNum
order by w.id, c.monthNum;
answered Nov 13 '18 at 14:22
Yogesh SharmaYogesh Sharma
28.9k51336
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
});
}
});
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%2f53282237%2fwhy-is-left-join-and-inner-join-returning-exact-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Not an efficient one, but works.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthNum,w2.*
from @calendar c CROSS JOIN (SELECT DISTINCT id FROM @whatjoins w)w
LEFT JOIN @whatjoins w2 ON w2.id = w.id AND c.monthNum=w2.monthnum
ORDER BY id
Only thing is I can't figure out an order by for this for the exact output you asked
ORDER BY ROW_NUMBER() OVER(PARTITION BY c.monthNum ORDER BY c.monthNum)
this is something I have come across but still not valid
answered Nov 13 '18 at 14:11
Biju joseBiju jose
19719
add a comment |
0
Not an efficient one, but works.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthNum,w2.*
from @calendar c CROSS JOIN (SELECT DISTINCT id FROM @whatjoins w)w
LEFT JOIN @whatjoins w2 ON w2.id = w.id AND c.monthNum=w2.monthnum
ORDER BY id
Only thing is I can't figure out an order by for this for the exact output you asked
ORDER BY ROW_NUMBER() OVER(PARTITION BY c.monthNum ORDER BY c.monthNum)
this is something I have come across but still not valid
answered Nov 13 '18 at 14:11
Biju joseBiju jose
19719
add a comment |
0
0
0
Not an efficient one, but works.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthNum,w2.*
from @calendar c CROSS JOIN (SELECT DISTINCT id FROM @whatjoins w)w
LEFT JOIN @whatjoins w2 ON w2.id = w.id AND c.monthNum=w2.monthnum
ORDER BY id
Only thing is I can't figure out an order by for this for the exact output you asked
ORDER BY ROW_NUMBER() OVER(PARTITION BY c.monthNum ORDER BY c.monthNum)
this is something I have come across but still not valid
answered Nov 13 '18 at 14:11
Biju joseBiju jose
19719
Not an efficient one, but works.
declare @calendar table
(
monthNum int
)
insert into @calendar
select 1 union
select 2 union
select 3 union
select 4
declare @whatjoins table
(
id varchar(3),
monthnum int,
total int
)
insert into @whatjoins
select 'aaa', 1, 400 union
select 'aaa', 2, 400 union
select 'aaa', 3, 400 union
select 'aaa', 4, 400 union
select 'bbb', 1, 500 union
select 'bbb', 3, 500 union
select 'bbb', 4, 999 union
select 'ccc', 1, 999 union
select 'ccc', 2, 999 union
select 'ccc', 4, 999
select
c.monthNum,w2.*
from @calendar c CROSS JOIN (SELECT DISTINCT id FROM @whatjoins w)w
LEFT JOIN @whatjoins w2 ON w2.id = w.id AND c.monthNum=w2.monthnum
ORDER BY id
Only thing is I can't figure out an order by for this for the exact output you asked
ORDER BY ROW_NUMBER() OVER(PARTITION BY c.monthNum ORDER BY c.monthNum)
this is something I have come across but still not valid
answered Nov 13 '18 at 14:11
Biju joseBiju jose
19719
answered Nov 13 '18 at 14:11
Biju joseBiju jose
19719
answered Nov 13 '18 at 14:11
Biju joseBiju jose
19719
answered Nov 13 '18 at 14:11
Biju joseBiju jose
19719
19719
add a comment |
add a comment |
0
You need CROSS JOIN before LEFT JOIN because of you want to repeat all monthnums for each ids :
select distinct c.monthNum, w.id, w1.monthnum, w1.total
from @whatjoins w cross join
@calendar c left join
@whatjoins w1
on w1.id = w.id and w1.monthnum = c.monthNum
order by w.id, c.monthNum;
answered Nov 13 '18 at 14:22
Yogesh SharmaYogesh Sharma
28.9k51336
add a comment |
0
You need CROSS JOIN before LEFT JOIN because of you want to repeat all monthnums for each ids :
select distinct c.monthNum, w.id, w1.monthnum, w1.total
from @whatjoins w cross join
@calendar c left join
@whatjoins w1
on w1.id = w.id and w1.monthnum = c.monthNum
order by w.id, c.monthNum;
answered Nov 13 '18 at 14:22
Yogesh SharmaYogesh Sharma
28.9k51336
add a comment |
0
0
0
You need CROSS JOIN before LEFT JOIN because of you want to repeat all monthnums for each ids :
select distinct c.monthNum, w.id, w1.monthnum, w1.total
from @whatjoins w cross join
@calendar c left join
@whatjoins w1
on w1.id = w.id and w1.monthnum = c.monthNum
order by w.id, c.monthNum;
answered Nov 13 '18 at 14:22
Yogesh SharmaYogesh Sharma
28.9k51336
You need CROSS JOIN before LEFT JOIN because of you want to repeat all monthnums for each ids :
select distinct c.monthNum, w.id, w1.monthnum, w1.total
from @whatjoins w cross join
@calendar c left join
@whatjoins w1
on w1.id = w.id and w1.monthnum = c.monthNum
order by w.id, c.monthNum;
answered Nov 13 '18 at 14:22
Yogesh SharmaYogesh Sharma
28.9k51336
answered Nov 13 '18 at 14:22
Yogesh SharmaYogesh Sharma
28.9k51336
answered Nov 13 '18 at 14:22
Yogesh SharmaYogesh Sharma
28.9k51336
answered Nov 13 '18 at 14:22
Yogesh SharmaYogesh Sharma
28.9k51336
28.9k51336
add a comment |
add a comment |
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%2f53282237%2fwhy-is-left-join-and-inner-join-returning-exact-results%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
What is the criteria for getting NULL in your expected result? Left join is working as expected.
– WhoamI
Nov 13 '18 at 14:18
The second query is an inner join. It returns the 10 rows that match with both tables. If it were a left join it would've ideally returned all 12 rows.
– fdkgfosfskjdlsjdlkfsf
Nov 13 '18 at 14:28