Left outer join in t-sql

Question

I have the following two tables. I am using SQL Server 2008 R2

Create table #tmp1 ( a char(1) ) Create table #tmp2 ( id int, a char(1), val int ) insert #tmp1 values ('A') insert #tmp1 values ('B') insert #tmp1 values ('C') insert #tmp2 values (1, 'A', 10) insert #tmp2 values (1, 'B', 20) insert #tmp2 values (2, 'A', 30) insert #tmp2 values (2, 'C', 40) select * from #tmp1 t1 left outer join #tmp2 t2 on t1.a = t2.a order by t2.id 

Returns a result set

 A 1 A 10 B 1 B 20 C 2 C 40 A 2 A 30 

I would like to have the following result set

  A 1 A 10 B 1 B 20 C 1 null null A 2 A 30 B 2 null null C 2 C 40 

Right now I understand this by creating a new table with cross join like this and then doing an outer join

 select * into #tmp3 from #tmp1 cross join (select distinct ID from #tmp2) t select * from #tmp3 t1 left outer join #tmp2 t2 on t1.a = t2.a and t1.id = t2.id 

Is there a better way to do this?

thanks

Answer 1

To get what you need, you need a “driving” table. That is, you want to get a complete list of all combinations, and then join the other tables to get matches. Here is one way:

 select t1.a, t2.* from (select t1.a as a, t2.id as id from (select distinct a from #tmp1 t1) t1 cross join (select distinct id from #tmp2 t2) t2 ) driving left outer join #tmp1 t1 on t1.a = driving.a left outer join #tmp2 t2 on t2.id = driving.id and t2.a = driving.a order by t2.id 

Answer 2

What you are looking for is the Cartesian product of the values ​​in # tbl1 and the values ​​in the id column in # tbl2. Since the values ​​in # tbl2.id are not unique, it might be better to have an extra table with a row for each value of # tbl2.id. Then you can use this solution:

 Create table #tmp1 ( a char(1) ) Create table #tmp2 ( id int, a char(1), val int ) Create table #tmp3 ( id int ) insert #tmp1 values ('A') insert #tmp1 values ('B') insert #tmp1 values ('C') insert #tmp3 values (1) insert #tmp3 values (2) insert #tmp2 values (1, 'A', 10) insert #tmp2 values (1, 'B', 20) insert #tmp2 values (2, 'A', 30) insert #tmp2 values (2, 'C', 40) SELECT t3.id,t1.a,t2.val FROM #tmp1 AS t1 CROSS JOIN #tmp3 AS t3 LEFT OUTER JOIN #tmp2 AS t2 ON t1.a = t2.a AND t3.id = t2.id ORDER BY t3.id, t1.a; 

If this is not an option, use this instead:

 SELECT t3.id,t1.a,t2.val FROM #tmp1 AS t1 CROSS JOIN (SELECT DISTINCT id FROM #tmp2) AS t3 LEFT OUTER JOIN #tmp2 AS t2 ON t1.a = t2.a AND t3.id = t2.id ORDER BY t3.id, t1.a;