SQL Server - Compare 2 tables for data in the same columns without checking for equality

Question

I asked this question before here, but the answer actually wasn't what I was looking for.

Suppose I have the following two tables in my SQL Server (2012) DB:

Tbl1:

ID:     Col1:     Col2:     Col3:
1       Val11     Val21     Val31
2       <NULL>    Val21     <NULL>
3       Val11     <NULL>    Val31
4       Val11     <NULL>    Val31

Tbl2:

ID:     Col1:     Col2:     Col3:
1       Val12     Val22     Val32
2       <NULL>    Val22     <NULL>
3       <NULL>    <NULL>    Val32
5       <NULL>    <NULL>    Val32

And, at this point, all I want to see is:

1. Any rows that are in one table but not the other (based on ID pk)
2. If the ID is in both tables, then are the same columns populated (not caring specifically about the values yet).

I'm just trying to come up with a SQL to just let me know which IDs have discrepancies.

My ideal output would look as follows:

Tbl1_ID:       Tbl2_Id:       Discrepancy:
1              1              0
2              2              0
3              3              1
4              <NULL>         1
<NULL>         5              1  

My testing SQL so far is this:

DECLARE 
@Tbl1 TABLE (ID INT, Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10))
;
DECLARE
@Tbl2 TABLE (ID INT, Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10))
;

INSERT INTO @Tbl1 (ID, Col1, Col2, Col3)
VALUES
    (1, 'Val11', 'Val21', 'Val31')
    ,(2, NULL   , 'Val21', NULL)
    ,(3, 'Val11', NULL, 'Val31')
    ,(4, 'Val11', NULL, 'Val31')
;

INSERT INTO @Tbl2 (ID, Col1, Col2, Col3)
VALUES
    (1, 'Val12', 'Val22', 'Val32')
    ,(2, NULL   , 'Val22', NULL)
    ,(3, NULL, NULL, 'Val32')
    ,(5, NULL, NULL, 'Val32')
;


SELECT
     [@Tbl1].ID AS Tbl1_ID
    ,[@Tbl2].ID AS Tbl2_ID
    , -- Some kind of comparison to let me know if all columns are populated the same
     AS Discrepancy
FROM
    @Tbl1
    FULL JOIN @Tbl2
    ON [@Tbl1].ID = [@Tbl2].ID

---

As you can see in the previous question's answer, the solution proposed (and I didn't check it well enough) was doing an ISNULL(Tbl1.Col1, xx) = ISNULL(Tbl2.Col1, xx) kind of comparison for the columns I'm looking for. The problem there is that it is, in fact, checking the values of the two tables against each other. All I want to do is check if they are both populated or not, without any need to do a value-comparison.

I know I could accomplish this with something along the lines of CASE WHEN Tbl1.Col1 is NULL THEN 1 ELSE 0 END = CASE WHEN Tbl2.Col1 IS NULL THEN 1 ELSE 0 END, but I'm hoping there is a nicer way to do this since I am checking a lot of columns.

Is there some good way to accomplish this?

Thanks!!

Answer 1

You could use:

SELECT
     t1.ID AS Tbl1_ID
    ,t2.ID AS Tbl2_ID
    ,CASE WHEN NOT EXISTS (
                -- here use template and put column list from table 1
        SELECT CASE WHEN t1.Col1 IS NOT NULL THEN -1 ELSE 0 END, 
               CASE WHEN t1.Col2 IS NOT NULL THEN -1 ELSE 0 END, 
               CASE WHEN t1.Col3 IS NOT NULL THEN -1 ELSE 0 END 
        INTERSECT 
               -- here use template and put column list from table 2
        SELECT CASE WHEN t2.Col1 IS NOT NULL THEN -1 ELSE 0 END, 
               CASE WHEN t2.Col2 IS NOT NULL THEN -1 ELSE 0 END, 
               CASE WHEN t2.Col3 IS NOT NULL THEN -1 ELSE 0 END)
     THEN 1 ELSE 0 END AS Discrepancy
FROM @Tbl1 t1
FULL JOIN @Tbl2 t2
    ON t1.ID = t2.ID;

Rextester.com Demo

It is a variant of IS DISTINCT FROM. If you care about actual values then:

SELECT
     t1.ID AS Tbl1_ID
    ,t2.ID AS Tbl2_ID
    ,CASE WHEN NOT EXISTS (
        SELECT t1.Col1, t1.Col2, t1.Col3
        INTERSECT 
        SELECT t2.Col1, t2.Col2, t2.Col3)
     THEN 1 ELSE 0 END AS Discrepancy
FROM @Tbl1 t1
FULL JOIN @Tbl2 t2
    ON t1.ID = t2.ID;