I have the following query that returns null values (from the left join) and also returns different amounts with the union clause. It returns 3 rows, which is correct.
Is there a non-complex way to remove the union clause so that it returns nulls and mismatches in column amount?
declare @table1 table
(
Id int,
Amount decimal(8,2)
)
declare @table2 table
(
Id int,
Amount decimal(8,2)
)
insert into @table1
select 1, 1.50 union
select 2, 2.50 union
select 3, 3.50 union
select 4, 4.50 union
select 5, 5.50
insert into @table2
select 1, 1.50 union
select 2, 2.75 union
select 3, 3.50
select t1.id, t1.amount, t2.id, t2.amount
from
@table1 t1 left join @table2 t2 on
t1.Id = t2.Id
--and t1.Amount <> t2.amount
where
t2.id is null
union
select t1.id, t1.amount, t2.id, t2.amount
from
@table1 t1 inner join @table2 t2 on
t1.Id = t2.Id
where
t1.Amount <> t2.amount
Result
id amount id amount
2 2.50 2 2.75
4 4.50 NULL NULL
5 5.50 NULL NULL
>Solution :
Presumably you’re after something like:
select t1.id, t1.amount, t2.id, t2.amount
from @table1 t1
left join @table2 t2 on t1.Id = t2.Id
where t1.Amount <> t2.amount or t2.id is null;