CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user829237
user829237

Reputation: 1759

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.

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
)

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.

The value fields can also have NULL values.

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?

Any suggestions?

Upvotes: 2

Views: 225

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

Nulls don't play nice with foreign keys: changing a null to a value will not (in SQL Server) cause it to cascade when updated.

Best to avoid the null value (and for many other reasons too!) Instead get the DBA to nominate some other 'magic' value of the same data type but outside of the domain type. Examples: DATE: far distant or far future date value. INTEGER: zero or negative value. VARCHAR: value in double-curly braces to denote meta data value e.g. '{{NONE}}', '{{UNKNOWN}}', '{{NA}}', etc then a CHECK constraint to ensure values cannot start/end with double curly braces.

Alternatively, model missing information by absence of a tuple in a relvar (closed world assumption) ;)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453327

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

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)

Upvotes: 1

Related Questions