T-SQL Issue with joins

Question

I am trying to achieve the Expected Result which is at the end of my post from two data sets. I am using left join which is not helping me to get my expected result. I would like to get all the matched and unmatched records from both tables.

Query used to create and retrieve dataset and my current result set are below:

select 
    a.id, a.name, a.rev, isnull(b.conv, 0) as conv 
from 
    (select 
        1 as id, 'A' as name, 2 as rev, 0 as conv

     union all

     select 
        2 as id, 'B' as name, 1 as rev, 0 as conv) a
left join
    (select 
         1 as id, 'A' as name, 0 AS rev, 2 as conv

     union all

     select 
         3 as id, 'C' as name, 0 as rev, 3 as conv) b on a.id = b.id

Current result from above query using left join:

id  name    rev conv
---------------------    
1   A        2   2
2   B        1   0

Expected result:

id  name    rev conv
--------------------
1   A        2    2
2   B        1    0
3   C        0    3

Answer 1

You should try with FULL OUTER JOIN, this combines the results of both LEFT and RIGHT joins.

Sintax:

SELECT column_name(s)
FROM 
     table1 FULL OUTER JOIN table2
ON 
     table1.column_name = table2.column_name;

I hope it helps!

Answer 2

Use FULL JOIN instead:

SELECT  ISNULL(a.id,b.id) id,
        ISNULL(a.name,b.name) name, 
        ISNULL(a.rev,0) rev, 
        ISNULL(b.conv,0) conv 
FROM (  SELECT 1 as id , 'A' as name , 2 as rev, 0 as conv
        UNION ALL
        SELECT 2 as id , 'B' as name, 1 as rev, 0 as conv) a
FULL JOIN ( SELECT 1 as id , 'A' as name, 0 AS rev, 2 as conv
            UNION ALL
            SELECT 3 as id , 'C' as name, 0 as rev, 3 as conv) b
    ON a.id = b.id
ORDER BY ISNULL(a.id,b.id);