sql server - sql aggregating the selection results -

i aggregate of selection results:

i have select user result1 from....) returning 2 columns:

user   results1 ****   ******** user1  5 user2  8 

i have query select user result2 from....) returning 2 columns:

user   results2 ****   ******** user1  9 user2  5 user3  15 

how join results 3 columns make like:

user   results1   results2 ****   ********   ******** user1  5          9 user2  8          5 user3            15 

if aren't sure user value exists in both tables, should use full join query tables. full join or full outer join (outer optional) return rows both tables, if user doesn't exist in 1 of them, still results:

select    [user] = coalesce(r1.[user], r2.[user]),   r1.results1,   r2.results2 result1 r1 full join result2 r2   on r1.[user] = r2.[user]; 

see sql fiddle demo.

the problem using inner join require user in both tables. if try use right join or left join - may still not return users , results.

if have multiple queries trying combine, can use cte or subqueries:

;with query1 (   select [user], result1 results1 ), query2 (   select [user], result2 results2 ) select    [user] = coalesce(q1.[user], q2.[user]),   q1.result1,   q2.result2 query1 q1 full join query2 q2   on q1.[user] = q2.[user]