Inner join returning more rows then exist in tables

Question

Till today my thoughts about the inner join were it will return the minimum number of rows that exist in tables satisfying a joining condition.

Ex. if table A contains 4 rows and table B contains 7 rows . i was expecting that 4 rows can be the maximum output if they satisfy the joining condition.

I just wrote an sp in which i was creating two temporary tables and was populating them. then i took an inner join of them but returning more rows (In my case 29 rows were returned i was expecting 4) After some search i found this link

which confirms that i can happen but i still wonder what are my options to limit the returned result.

Below is my stored procedure.

ALTER PROCEDURE [dbo].[GetDDFDetailOnSiteCol]
@siteId int,
@colNum int
AS
BEGIN
SET NOCOUNT ON;

create Table #portDetail
(
ddfId int,
portDetail nvarchar(50),
siteId int
)
Insert into #portDetail SELECT  ddf.id,  ddf.portDetail, site.Site_ID  from site
        inner join ddf ON site.Site_ID = ddf.siteCodeID 
        where ddf.siteCodeID = @siteId and ddf.colNo= @colNum
        order by colNo,blockNum,portRowNum,portColNum

create Table #portAllocationDetail
(
assigned_slot nvarchar(50),
siteId int
)
Insert into #portAllocationDetail 
SELECT  dbo.portList.assigned_slot, dbo.site.Site_ID
FROM dbo.portList INNER JOIN
 dbo.site ON dbo.portList.siteCodeID = dbo.site.Site_ID
 where dbo.site.Site_ID = @siteId

--select * from #portAllocationDetail   
Select #portDetail.ddfId,#portDetail.portDetail,#portAllocationDetail.siteId,#portAllocationDetail.assigned_slot FROM #portDetail 
INNER JOIN #portAllocationDetail 
ON
#portDetail.siteId = #portAllocationDetail.siteId
END

Answer 1

An inner join repeats each matching row in TableB for each row in TableA. So if there are 4 rows in TableA, and 7 in TableB, the maximum rowcount is 28.

Example at SQL Fiddle.