查找更改的行(带空值的复合键) [英] Find changed rows (composite key with nulls)

问题描述

我正在尝试构建一个查询，该查询将从源表中获取所有更改的行，并将其与目标表进行比较.

Im trying to build a query that will fetch all changed rows from a source table, comparing it to a target table.

主键(它并没有真正定义为主键，只是我们所知道的标识了一个唯一的行)是一个由许多外键组成的组合.大约 15 个，其中大部分可以有 NULL 值.为简单起见，假设主键由这三个键列组成，并有 2 个需要比较的值字段:

The primary key (its not really defined as a primary key, just what we know identifies an unique row) is a composite that consists of lots of foreign keys. Aproximatly about 15, most of which can have NULL values. For simplicity lets say the primary key consists of these three key columns and have 2 value fields that needs to be compared:

CREATE TABLE SourceTable 
(
    Key1 int NOT NULL,
    Key2 nvarchar(10),
    Key3 int,
    Value1 nvarchar(255),
    Value2 int
)

如果 Key1 = 1，Key2 = NULL 和 Key3 = 4.那么我想将它与 target 中关键字段中具有完全相同值的行进行比较.在键 2 中包含 NULL.

If Key1 = 1, Key2 = NULL and Key3 = 4. Then I would like to compare it to the row in target that has exactly the same values in the key fields. Including NULL in key 2.

值字段也可以有 NULL 值.

The value fields can also have NULL values.

那么在设计这样的查询时使用的最佳方法是什么，其中应将 NULL 值视为真实值并进行比较?一片空白?合并?相交?

So whats the best approach to use when designing queries like this where NULL values should be considered as real values and compared? ISNULL? COALESCE? Intersect?

有什么建议吗?

推荐答案

ANSI SQL 具有尚未在 SQL Server 中实现的 IS [NOT] DISTINCT FROM 构造(连接请求).

ANSI SQL has the IS [NOT] DISTINCT FROM construct that has not been implemented in SQL Server yet (Connect request).

可以在 SQL Server 中使用 EXCEPT/INTERSECT 模拟此功能.这两者在比较中都将 NULL 视为相等.您想要查找键列相同但值列不同的行.所以应该这样做.

It is possible to simulate this functionality in SQL Server using EXCEPT/INTERSECT however. Both of these treat NULL as equal in comparisons. You are wanting to find rows where the key columns are the same but the value columns are different. So this should do it.

SELECT *
FROM   SourceTable S
       JOIN DestinationTable D
         ON S.Key1 = D.Key1
            /*Join the key columns on equality*/
            AND NOT EXISTS (SELECT S.Key2,
                                   S.Key3
                            EXCEPT
                            SELECT D.Key2,
                                   D.Key3)  
             /*and the value columns on unequality*/
            AND NOT EXISTS (SELECT S.Value1,
                                   S.Value2
                            INTERSECT
                            SELECT D.Value1,
                                   D.Value2)  

这篇关于查找更改的行(带空值的复合键)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！