Three ways to create number combinations


create table test(id int)
insert into test values (1), (2), (3), (4)

--Option 1 Cross join
select * from test t1, test  t2
where t1.id<>t2.id
Order by t1.id,t2.id

 --or
 select * from test t1 CROSS JOIN test  t2
where t1.id<>t2.id
 Order by t1.id,t2.id

--Option 2  cross apply
select * from test t1
cross apply test  t2
where t1.id<>t2.id
Order by t1.id,t2.id

--Option 3 inner join
SELECT *  FROM  test t1
    INNER JOIN  test t2
	  ON t1.id <>t2.id
 Order by t1.id,t2.id

drop table test

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s