sql aggregating the selection results

Question

I would like to aggregate all of my selection results:

I have a select user result1 from....) returning 2 columns:

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

I also have another query select user result2 from....) returning another 2 columns:

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

How would I join the results together with 3 columns to make something like:

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

Answer 1

If you aren't sure that the User value exists in both tables, then you should use a FULL JOIN to query the tables. A FULL JOIN or FULL OUTER JOIN (OUTER is optional) will return the rows from both tables, but if the user doesn't exist in one of them, you will still get results:

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

See SQL Fiddle with Demo.

The problem with using an INNER JOIN is it will require that the User is in both tables. If you try to use a RIGHT JOIN or LEFT JOIN - then you may still not return all users and their results.

If you have multiple queries that you are trying to combine, then you can use a CTE or subqueries:

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

Answer 2

You should use FULL OUTER JOIN

select b.User, a.Result1, b.Result2 
from table1 A
full outer join table2 b 
where a.user = b.user

a Full outer Join will give you all records only in table a and b + common records.