英文:
Left join to find nulls and non-matches
问题
以下是翻译好的部分：
我有以下查询，它返回了空值（来自左连接），并且还使用union子句返回不同的金额。它返回3行，这是正确的。
是否有一种非复杂的方法可以去掉union子句，以便它返回列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
结果
id amount id amount
2 2.50 2 2.75
4 4.50 NULL NULL
5 5.50 NULL NULL
英文:
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
答案1
得分: 3
以下是翻译好的部分：
假设您想要类似以下的内容：
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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论