I was reading about Joins in Kalen Delaney’s Microsoft SQL Server Internals 2012 and came across a point “a nested loops join is the only join algorithm that SQL Server can use without atleast one equijoin predicate”. This means that regardless of the dataset size or indexes, if no equality predicate exists in join condition, the optimizer will choose only ‘Nested Loop’ physical operator. Below test case verifies the same:
Create Table #Test1(sno int identity(1,1) primary key,sname varchar(6) default 'table1')
Create Table #Test2(sno int identity(5000,1) primary key,sname varchar(6) default 'table1')
GO
Insert into #Test1 Values (default)
Go 1000
Insert into #Test2 Values (default)
Go 1000
/*If no equality predicate is present in the join condition, then nested loop join is the only physical operator optimizer can use*/
Select A.sname,B.sname from #Test1 A Inner Join #Test2 B on A.sno<>B.sno and A.sname<>b.sname
Drop table #Test1
Drop table #Test2
Below is the execution plan:
SQL Server Repository 