Bala Bala - 25 days ago 14
SQL Question

How to get specific mismatched column when using EXCEPT

I am using

EXCEPT / MINUS
to compare if rows/columns between two tables are matching.

select col1, col2 from table1
minus
select col1, col2 from table2


For a given row, if for e.g.
col2
doesn't match, then I would like to output only
col2
ignoring matched
col1
value or show null as there are no mismatches for
col1
. Is there a way to do that with SQL alone?

Table1

| col1 | col2 |
| A | B |
| X | Y |
| M | N |


Table 2

| col1 | col2 |
| A | Z |
| X | Y |
| 1 | N |


Applying the above sql produces

| col1 | col2 |
| A | B |
| M | N |


But what I might need is only mismatched values as below.

| col1 | col2 |
| | B |
| M | |

Answer

You can try this

SELECT CASE 
         WHEN T2.COL1 IS NULL OR T1.col1 <> T2.col1 THEN T1.col1 
       END COL1, 
       CASE 
         WHEN T2.COL2 IS NULL OR T1.col2 <> T2.col2 THEN T1.col2 
       END COL2 
FROM   table1 T1 
       LEFT JOIN table2 T2 
         ON (T1.col1 = T2.col1 
             OR T1.col2 = T2.col2)
            AND (T1.col1 <> T2.col1 
                  OR T1.col2 <> T2.col2); 

Hope this should solve your problem.

Comments