如何用单条SQL查询实现Ours与Partner表的特征差异比对？

在SQL Server数据库中，有两张存储车辆信息的表：

1. 我方数据库中的Ours表，结构及数据如下：

Dealer  RegID   Feature       Included 
--------------------------------------
CarsRus 312904  A41:Insured   1
CarsRus 292965  A41:Insured   1
CarsRus 146278  A41:Insured   0
CarsRus 660838  A41:Insured   0
CarsRus 216326  A41:Insured   1

ChpCars 312904  A41:Insured   0
ChpCars 292965  A41:Insured   1
ChpCars 146278  A41:Insured   1
ChpCars 660838  A41:Insured   0
ChpCars 216326  A41:Insured   0

AutoMob 312904  A41:Insured   0
AutoMob 292965  A41:Insured   1
AutoMob 146278  A41:Insured   1
AutoMob 660838  A41:Insured   1
AutoMob 216326  A41:Insured   0

CarsRus 312904  M38:Serviced   0
CarsRus 292965  M38:Serviced   0
CarsRus 146278  M38:Serviced   0
CarsRus 660838  M38:Serviced   0
CarsRus 216326  M38:Serviced   1

ChpCars 312904  M38:Serviced   1
ChpCars 292965  M38:Serviced   0
ChpCars 146278  M38:Serviced   1
ChpCars 660838  M38:Serviced   1
ChpCars 216326  M38:Serviced   0

AutoMob 312904  M38:Serviced   1
AutoMob 292965  M38:Serviced   1
AutoMob 146278  M38:Serviced   1
AutoMob 660838  M38:Serviced   0
AutoMob 216326  M38:Serviced   1


CarsRus 312904  Y77:Cleaned   1
CarsRus 292965  Y77:Cleaned   0
CarsRus 146278  Y77:Cleaned   0
CarsRus 660838  Y77:Cleaned   1
CarsRus 216326  Y77:Cleaned   0

ChpCars 312904  Y77:Cleaned   0
ChpCars 292965  Y77:Cleaned   1
ChpCars 146278  Y77:Cleaned   1
ChpCars 660838  Y77:Cleaned   1
ChpCars 216326  Y77:Cleaned   0

AutoMob 312904  Y77:Cleaned   0
AutoMob 292965  Y77:Cleaned   0
AutoMob 146278  Y77:Cleaned   1
AutoMob 660838  Y77:Cleaned   1
AutoMob 216326  Y77:Cleaned   0

2. 合作伙伴提供的Partner表（仅对应一家经销商，无Dealer字段），结构及数据如下：

RegID   Feature       Included
------------------------------
312904  A41:Insured   1
292965  A41:Insured   0
146278  A41:Insured   1
660838  A41:Insured   1
216326  A41:Insured   0

312904  M38:Serviced   0
292965  M38:Serviced   1
146278  M38:Serviced   0
660838  M38:Serviced   1
216326  M38:Serviced   0

312904  Y77:Cleaned   1
292965  Y77:Cleaned   0
146278  Y77:Cleaned   0
660838  Y77:Cleaned   1
216326  Y77:Cleaned   0

需求是找出两张表中Included特征的差异，期望输出格式如下：

Dealer  RegID   Feature       Ours  Partner
-------------------------------------------
CarsRus 292965  A41:Insured   1     0
CarsRus 146278  A41:Insured   0     1
CarsRus 660838  A41:Insured   0     1
CarsRus 216326  A41:Insured   1     0

ChpCars 312904  A41:Insured   0     1
ChpCars 292965  A41:Insured   1     0
ChpCars 660838  A41:Insured   0     1


AutoMob 312904  A41:Insured   0      1
AutoMob 292965  A41:Insured   1      0

CarsRus 292965  M38:Serviced   0      1
CarsRus 660838  M38:Serviced   0      1
CarsRus 216326  M38:Serviced   1      0

ChpCars 312904  M38:Serviced   1   0
ChpCars 292965  M38:Serviced   0   1
ChpCars 146278  M38:Serviced   1   0

AutoMob 312904  M38:Serviced   1   0
AutoMob 146278  M38:Serviced   1   0
AutoMob 660838  M38:Serviced   0   1
AutoMob 216326  M38:Serviced   1   0

ChpCars 312904  Y77:Cleaned   0   1
ChpCars 292965  Y77:Cleaned   1   0
ChpCars 146278  Y77:Cleaned   1   0

AutoMob 312904  Y77:Cleaned   0   1
AutoMob 146278  Y77:Cleaned   1   0

当前通过临时表分步实现：

DECLARE @T AS TABLE
              (
                  Dealer varchar(50), 
                  RegID bigint, 
                  Feature varchar(50), 
                  Ours int, 
                  Partner int
              )

先将Ours左连接Partner，插入Partner.RegID为空的记录到临时表；再将Partner左连接Ours，更新临时表。但这种方式不够高效，希望能用单条SQL查询实现需求。

可以使用FULL JOIN结合过滤条件，一次性获取所有Included字段不一致的记录，同时保留Dealer信息：

SELECT
    COALESCE(o.Dealer, (SELECT DISTINCT Dealer FROM Ours)) AS Dealer,
    COALESCE(o.RegID, p.RegID) AS RegID,
    COALESCE(o.Feature, p.Feature) AS Feature,
    ISNULL(o.Included, 0) AS Ours,
    ISNULL(p.Included, 0) AS Partner
FROM Ours o
FULL JOIN Partner p
    ON o.RegID = p.RegID
    AND o.Feature = p.Feature
WHERE ISNULL(o.Included, -1) != ISNULL(p.Included, -1)
ORDER BY Feature, Dealer, RegID

思路说明 #

1. FULL JOIN：同时匹配两张表中RegID和Feature相同的记录，也会保留仅在某一张表中存在的记录。
2. COALESCE函数：处理Dealer、RegID、Feature的空值情况，确保输出字段不为空。其中Dealer如果来自Partner表（无该字段），则取Ours表中任意经销商（因为Partner对应一家经销商，所有经销商都需要和同一份Partner数据对比）。
3. ISNULL函数：将Included的空值转换为0（可根据业务逻辑调整默认值），方便比较差异。
4. WHERE过滤条件：筛选出Included值不一致的记录，用-1作为临时空值标记，确保单表存在的记录也被纳入差异范围。
5. ORDER BY：按Feature、Dealer、RegID排序，让输出格式与期望一致。

如果Partner表确实只对应某一家特定经销商，可以调整COALESCE中的Dealer取值，比如指定为该经销商名称：

COALESCE(o.Dealer, 'TargetDealer') AS Dealer

内容的提问来源于stack exchange，提问作者asmgx