EXCEPT you can quickly and efficiently compare whether two columns are equal (including when they are both
NULL). Using this method if the column on both sides is
NULL then they will be treated as equal; and if one side is
NULL and the other not, they will be correctly treated as not equal.
SELECT ... FROM ... WHERE EXISTS( SELECT [Column_A] EXCEPT SELECT [Column_B] );
The biggest power of this is realised when combining it with the
MERGE statement. By using
AND EXISTS(... EXCEPT ...) within the
WHEN MATCHED ... portion of the merge, SQL automatically checks for
NULL‘s on both sides. This is more performant than using
ISNULL(...) and the not equal to operator
... ..., as SQL can now take advantage of any indexes that exist.
MERGE INTO [TargetTable] AS [T] USING [SourceTable] AS [S] ON ([T].[ID] = [S].[ID]) WHEN MATCHED AND EXISTS ( SELECT [T].[Code] ,[T].[Description] COLLATE Latin1_General_CS_AS EXCEPT SELECT [S].[Code] ,[S].[Description] COLLATE Latin1_General_CS_AS ) THEN UPDATE SET [T].[Code] = [S].[Code] ,[T].[Description] = [S].[Description] WHEN NOT MATCHED BY TARGET THEN INSERT ([ID], [Code], [Description]) VALUES ([ID], [Code], [Description]) WHEN NOT MATCHED BY SOURCE THEN DELETE;