Why is LEFT JOIN and INNER JOIN returning exact results?

user_4191466 0
Viewed 85

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-server-2008 tsql sql-server sql
2 Answers

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

user_14210500

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;
user_55294450
Related