Click here to Skip to main content
15,029,715 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,

the below result count is 8 records.

declare @t table (id int)
declare @t2 table(id2 int)
insert into @t values(1),(1),(1)
insert into @t2 values(1),(1),(1)
select t1.id from @t t1 inner join @t2 t2 on t1.id=t2.id2

I think the count should be 9 records

Please suggest.

Thanks

What I have tried:

Inner Join Result is different
Posted
Updated 25-Jul-18 21:21pm
Comments
CHill60 26-Jul-18 4:11am
   
When I run your code I get 9 rows
Richard Deeming 26-Jul-18 12:17pm
   
REPOST
You have already posted this in the database forum:
https://www.codeproject.com/Messages/5540480/inner-join-results.aspx[^]

declare @t table (id int)
declare @t2 table(id2 int)
insert into @t values(1),(1),(1)
insert into @t2 values(1),(1),(1)

select a.* from(
select row_number() over(order by id) as Sno,* from @t) as a
inner join(
select row_number() over(order by id2) as Sno,* from @t2) as b
on a.sno=b.sno
   
Comments
Member 13867163 26-Jul-18 2:54am
   
Hi,

Thank you for your reply.
My doubt here is the inner join result is giving 8 records as per my query.
my doubt is as per row by row joining it should be 9 records i think. why 8 records are given. any logic internally.

Please suggest
can you share the query with me so i can check.but in inner join the query will preform as cross join because every row id is same as next row id so that the inner join will join every record in our table column id.
   
Comments
Member 13867163 26-Jul-18 3:11am
   
Exactly. So my doubt is in First Table I have 3 Ids and in second table I have 3 Ids.
So 3X3=9 records should come. But while executing the query I am getting 8 records.
CHill60 26-Jul-18 4:10am
   
Don't post comments or questions as solutions! Either use the "Have a Question or Comment?" link next to a post or use the "Reply" link next to a comment
that is not possible
only one case can possible for that when one is not match
   
Comments
Member 13867163 26-Jul-18 4:23am
   
No what I mean to say is I have 3 ids are there in table1 I am joining with table 2
row by row base 3 ids in table1 is joined to table2 as below.

First id 1 having matches found 3 ids in table2 so the records are 3 rows.
second id 1 having match found 3 ids in table2 so the records are 3 rows
third id 1 having match found 3 ids in table2 so the records are 3 rows

So totol is 9 records.
But why it is giving 8 records.

Please suggest

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900