Compare multiple columns in SQL and determine which are wrong

Question

I have two tables A and B which can be join. Both tables contains many columns but there are 6 columns in both tables, that should contain the same values. These columns are not foreign or primary keys. They are just ordinary columns and they can contain NULL value.

Lets assume these columns are named like this: A_1 ... A_6, B_1 ... B_7

My task is to compare all these 6 columns, write them down in the answer and also write down which columns do not match.

Which means that in the query result there must be these 6 columns from A, six columns from B and then another column that says for example: "ERROR is in A_5"

I could do something like this:

SELECT 
    A_1, A_2, A_3, A_4, A_5, A_6, 
    B_1, B_2, B_3, B_4, B_5, B_6,
    CASE
        WHEN A_1 != B_1 THEN 'ERROR is in A_1'
        WHEN A_2 != B_2 THEN 'ERROR is in A_2'
        WHEN A_3 != B_3 THEN 'ERROR is in A_3'
        WHEN A_4 != B_4 THEN 'ERROR is in A_4'
        WHEN A_5 != B_5 THEN 'ERROR is in A_5'
        WHEN A_6 != B_6 THEN 'ERROR is in A_6'
    END
    as ERROR

FROM A
LEFT JOIN B
   ON A.B_CODE = B.CODE
WHERE
   A_1 != B_1 OR A_2 != B_2 OR A_3 != B_3 OR
   A_4 != B_4 OR A_5 != B_5 ORA_6 != B_6;

But there would have to be 51 another when statements, becasue A_1 and A_2 could be wrong both so I would also need to check all pairs and all threes and so on.

Question is: Is there any way how to do this more efectively and more "cleaner" way?

The whole problem is in that ERROR column, because it must be in the result

Answer 1

The problem I see here is that the A and B tables are not normalized. This makes it tough to compare them in the form given - so, let's use common table expressions to normalize them (note here that I've got A and B as CTE's as well - I'm too lazy to create actual tables, just remove the A and B CTE's if you need to):

WITH A AS (SELECT 99 AS CODE, 1 AS A_1, 2 AS A_2,  3 AS A_3, 4 AS A_4, 5 AS A_5, 6 AS A_6 FROM DUAL),
     B AS (SELECT 99 AS CODE, 1 AS B_1, 2 AS B_2, 13 AS B_3, 4 AS B_4, 5 AS B_5, 6 AS B_6 FROM DUAL),
     A1 AS (SELECT CODE, 1 AS COLNUM_A, A_1 AS VAL_A FROM A),
     A2 AS (SELECT CODE, 2 AS COLNUM_A, A_2 AS VAL_A FROM A),
     A3 AS (SELECT CODE, 3 AS COLNUM_A, A_3 AS VAL_A FROM A),
     A4 AS (SELECT CODE, 4 AS COLNUM_A, A_4 AS VAL_A FROM A),
     A5 AS (SELECT CODE, 5 AS COLNUM_A, A_5 AS VAL_A FROM A),
     A6 AS (SELECT CODE, 6 AS COLNUM_A, A_6 AS VAL_A FROM A),
     B1 AS (SELECT CODE, 1 AS COLNUM_B, B_1 AS VAL_B FROM B),
     B2 AS (SELECT CODE, 2 AS COLNUM_B, B_2 AS VAL_B FROM B),
     B3 AS (SELECT CODE, 3 AS COLNUM_B, B_3 AS VAL_B FROM B),
     B4 AS (SELECT CODE, 4 AS COLNUM_B, B_4 AS VAL_B FROM B),
     B5 AS (SELECT CODE, 5 AS COLNUM_B, B_5 AS VAL_B FROM B),
     B6 AS (SELECT CODE, 6 AS COLNUM_B, B_6 AS VAL_B FROM B)
SELECT 'Error comparing A' || COLNUM_A || ' (value=' || VAL_A || ') to B' || COLNUM_B || ' (value=' || VAL_B || ')' AS ERR_TEXT
  FROM (SELECT * FROM A1 INNER JOIN B1 USING (CODE)
        UNION ALL
        SELECT * FROM A2 INNER JOIN B2 USING (CODE)
        UNION ALL
        SELECT * FROM A3 INNER JOIN B3 USING (CODE)
        UNION ALL
        SELECT * FROM A4 INNER JOIN B4 USING (CODE)
        UNION ALL
        SELECT * FROM A5 INNER JOIN B5 USING (CODE)
        UNION ALL
        SELECT * FROM A6 INNER JOIN B6 USING (CODE))
  WHERE VAL_A <> VAL_B;  

This changes the problem from something that is awkward in a relational database (that is, comparing a bunch of different fields in a few rows) to something that is reasonably clean (joining tables and comparing a few columns in many rows).

SQLFiddle here

Share and enjoy.

Answer 2

Can you do something like this?

SELECT
    A_1, A_2, A_3, A_4, A_5, A_6, 
    B_1, B_2, B_3, B_4, B_5, B_6,
    LEFT(ERROR, LEN(ERROR) - 1) AS ERROR
FROM (
SELECT
    A_1, A_2, A_3, A_4, A_5, A_6, 
    B_1, B_2, B_3, B_4, B_5, B_6,
    'Error is in positions ' 
        + CASE WHEN ERROR_1 = 1 THEN '1,' ELSE '' END
        + CASE WHEN ERROR_2 = 1 THEN '2,' ELSE '' END
        + CASE WHEN ERROR_3 = 1 THEN '3,' ELSE '' END
        + CASE WHEN ERROR_4 = 1 THEN '4,' ELSE '' END
        + CASE WHEN ERROR_5 = 1 THEN '5,' ELSE '' END
        + CASE WHEN ERROR_6 = 1 THEN '6,' ELSE '' END AS ERROR
FROM (
SELECT 
    A_1, A_2, A_3, A_4, A_5, A_6, 
    B_1, B_2, B_3, B_4, B_5, B_6,
    CASE WHEN ISNULL(A_1, '') != ISNULL(B_1, '') THEN 1 END AS ERROR_1,
    CASE WHEN ISNULL(A_2, '') != ISNULL(B_2, '') THEN 1 END AS ERROR_2,
    CASE WHEN ISNULL(A_3, '') != ISNULL(B_3, '') THEN 1 END AS ERROR_3,
    CASE WHEN ISNULL(A_4, '') != ISNULL(B_4, '') THEN 1 END AS ERROR_4,
    CASE WHEN ISNULL(A_5, '') != ISNULL(B_5, '') THEN 1 END AS ERROR_5,
    CASE WHEN ISNULL(A_6, '') != ISNULL(B_6, '') THEN 1 END AS ERROR_6
FROM
    A
    LEFT JOIN B ON A.B_CODE = B.CODE
WHERE
   A_1 != B_1 OR A_2 != B_2 OR A_3 != B_3 
   OR A_4 != B_4 OR A_5 != B_5 OR A_6 != B_6) I) O;

It probably could look a lot nicer than that (by using common-table expressions for a start), but my Oracle SQL is a bit rusty so I kept it simple.