We have 2 versions of a table: V1 and V2, V1 being the source of truth. I am in the process of comparing both these versions and trying to make V2 a mirror copy of V1 (there is no possibility of deleting all the contents of V2 and copying from V1).

Now, I found that there are lots of missing rows in V2 for a specific date and I need to pull them from V1 for that date. To find out what rows are available in V1 and missing in V2, I am trying LEFT EXCLUDING JOIN:

SELECT * 
FROM V1
LEFT JOIN
(
    SELECT * FROM V2  
    WHERE 
    Status IN ('A','P','S')
    AND Date = '2015-06-30'
) V2 ON V1.[Level 1 Code] = V2.[L1Code] COLLATE SQL_Latin1_General_CP1_CI_AS
     AND V1.[Date] = V2.Date
     AND V1.[Unique Identifier] = V2.Identifier COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE
    V1.[Status] IN ('A','P','S')
    AND V1.[Date] = '2015-06-30'
    AND V2.Id IS NULL

Using this code, no row is returned. However, If I replace the Status filter to

Status = 'S'

instead of (‘A’,’P’,’S’) then it returns a row which is missing from V2, which seems right. But I need to test it for all the three statuses together.

I wanted to know why it does not work with the current way. Any help is much appreciated.

Thanks,
Kirti

I have verified to the more granular levels to do further testing and found that Status = ‘S’ works fine instead of Status in (‘A’,’P’,’S’)

I am expecting an explanation of this, or may be the correct code if my code is incorrect.