WHEN MATCHED clause fails if there's a NULL in the ON columns I'm using a query with merge that works fine if all columns in the ON clause have a value but WHEN MATCHED omits if there's a NULL mark in one of the columns:

MERGE PEPS AS peps
USING (Lots of Cols..
       FROM PEPS_temp) 
AS temp (Lots of Cols..) 
ON peps.[Name] = temp.[Name] AND 
   peps.[LastName] = temp.[LastName] AND 
   peps.[TypeEntity] = temp.[TypeEntity] AND
   peps.[Source] = temp.[Source]
WHEN MATCHED THEN
UPDATE SET peps.[Category] = temp.[Category] 
WHEN NOT MATCHED THEN
INSERT (Lots of Cols..) VALUES (temp.[Col], temp.[Col], temp[Col]....)


Case: I have target table that I update manually first its Category column so I set Category='EX BLOCKED' the source table is the updated list so the clause WHEN MATCHED is supposed to update the Category column to 'BLOCKED' if it exists in the source table, if not WHEN NOT MATCHED inserts the new rows that do not exist.

Query runs successfully and when checking the lists I detect duplicates:

;WITH Duplicados
     AS (Select ROW_NUMBER() OVER  (PARTITION BY Name, LastName, TypeEntity, Source ORDER BY ( SELECT 0)) RowNum, Name, LastName, TypeEntity, Source
         FROM [PEPS])
SELECT * FROM Duplicados
WHERE  RowNum &gt; 1 AND Source='OFAC SDN';


All the duplicate rows contain NULL marks in at least one of the columns of the ON clause.

Example:
The predicate for MATCH doesn't fail, it behaves exactly like it is supposed to. NULL is not a value, NULL is a state that indicates there is no value. 
Therefore, any predicate with any comparison to NULL will resolve to 'UNKNOWN' and not to 'TRUE', causing the row to be considered as a non-match.
To deal with that, you will need to decide on the logic and how you would like to evaluate the rows in case you have NULLs. 
For example, if It is only for last name, and you want to consider it a match, you may solve it by extending the predicate to handle the NULL:

MERGE PEPS AS peps
USING (Lots of Cols..
       FROM PEPS_temp) 
AS temp (Lots of Cols..) 
ON peps.[Name] = temp.[Name] AND 
   (peps.[LastName] = temp.[LastName] OR peps.LastName IS NULL) AND 
   peps.[TypeEntity] = temp.[TypeEntity] AND
   peps.[Source] = temp.[Source]
WHEN MATCHED THEN
UPDATE SET peps.[Category] = temp.[Category] 
WHEN NOT MATCHED THEN
INSERT (Lots of Cols..) VALUES (temp.[Col], temp.[Col], temp[Col]....)