CODEWITHSUNDEEP
sqlsql-server
vpascoal
vpascoal

Reputation: 278

SQL Query get all values from left table and uncommon values from right table

I want to get all values from the left table (ignoring the common values from the right table with the same id) and all values from the right table that don't exist in left table.

Table 1:

----------------------
|   id   |    value  |
----------------------
|   1    |    50     |
----------------------
|   2    |    150    |
----------------------
|   4    |    100    |
----------------------

Table 2:

----------------------
|   id   |    value  |
----------------------
|   1    |    300    |
----------------------
|   3    |    150    |
----------------------
|   4    |    250    |
----------------------

Expected result:

----------------------
|   id   |    value  |
----------------------
|   1    |    50     |
----------------------
|   2    |    150    |
----------------------
|   3    |    150    |
----------------------
|   4    |    100    |
----------------------

Is there any easy way to do this?

Thank you.

Upvotes: 0

Views: 802

Answers (4)

Aun
Aun

Reputation: 1923

select distinct *
from
(select id, value
from table1 t1
union all
select id, value
from table2 t2)

Upvotes: 0

Dgan
Dgan

Reputation: 10285

select * from 
(
select * from tab1
except
select * from tab2
UNION ALL
select id, value
from tab2 t2
where not exists (select 1 from tab1 t1 where t1.id = t2.id))d
order by d.id ;

output

id  value
1   50
2   150
3   150
4   100

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You can also do this with a Full Join and a Coalesce

Select      Coalesce(L.Id, R.Id) Id,
            Coalesce(L.Value, R.Value) Value
From        Table1  L
Full Join   Table2  R On L.Id = R.Id
Order By    Id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use union all and a not exists clause:

select id, value
from table1 t1
union all
select id, value
from table2 t2
where not exists (select 1 from table1 t1 where t1.id = t2.id);

Upvotes: 5

Related Questions