Binaryworld - Generate combinations of data from 2 tables & match against a combinat ... [ SQL Server -> T-SQL ]

Demonstrates some powerful derived table & ANSI join features. The script shows how to generate combinations of values from 2 tables & match them against a third table.

Click here to copy the following block

create table #a ( a int );
create table #b ( b int );
-- This table contains all combinations of the values from #a & #b
create table #ab ( a int , b int );
go
insert #a values( 1 );
insert #a values( 2 );
select * from #a;
/*
a
-----------
     1
     2
*/
insert #b values( 5 );
insert #b values( 6 );
select * from #b;
/*
b
-----------
     5
     6
*/
insert #ab values( 1 , 5 );
insert #ab values( 2 , 6 );
select * from #ab;
/*
a      b
----------- -----------
     1      5
     2      6
*/
go
-- Objective: Get all combinations of rows from #a & #b irrespective of
--      whether there is one existing in the combinations table.
-- Solution : This one uses one of the least known features of the ANSI joins
--      i.e., using parenthesis to nest joins & build virtual results.
select #a.a , #b.b , #ab.a AS "ab-a" , #ab.b as "ab-b"
from #ab
full join ( #a cross join #b )
on #ab.a = #a.a And #ab.b = #b.b
-- Expected Output:
/*
a      b      ab-a    ab-b
----------- ----------- ----------- -----------
     1      5      1      5
     1      6 NULL    NULL
     2      5 NULL    NULL
     2      6      2      6
*/
go
drop table #a;
drop table #b;
drop table #ab;
go