Preparing a compare query between two database tables with similar columns

Given two tables each having n columns where 1 to n-1 columns are the columns which together form a unique key and the nth column is the value column, how can I create a generalized query in my java program which outputs 1 to n-1 columns, nth column of first table, nth column of second table and the difference of the nth column of the two tables.

For e.g. when n = 2, that is when given tables have two columns where first column is the key and the second column is the value, my query would be like:

SELECT 
  COALESCE(A.COL1, B.COL1) AS COL1, 
  CAST(A.VALCOL AS DECIMAL(18,2)) AS SRC_TAB_VALUE, 
  CAST(B.VALCOL AS DECIMAL(18,2)) AS TRGT_TAB_VALUE, 
  CAST(CAST(A.VALCOL AS DECIMAL(18,2)) - cast(B.VALCOL AS DECIMAL(18,2)) AS DECIMAL(18,2)) AS DIFF_OF_VAL 
FROM 
  SCHEMA1.TAB1 A 
FULL JOIN 
  SCHEMA2.TAB2 B 
ON A.COL1 = B.COL1 
  ORDER BY COL1

I am preparing above query in my Java program. The diff query above runs against two in memory HSQL tables.

But how can I generalize this query when the number of key columns are greater then 1.

e.g. given two tables with 5 columns each, the combination of first 4 columns will create a unique key while the fifth column would be the value of that key which is to be compared.

Thanks for reading!

Solution

Approaching your question differently, here is another answer.

If you want the SQL to be as generic as possible so that it is easy to generate in your Java, you could write it like this:

WITH 
  TABLEA (COL1,COL2,COLN) AS (SELECT * FROM SCHEMA1.TAB1),
  TABLEB (COL1,COL2,COLN) AS (SELECT * FROM SCHEMA2.TAB2)        
SELECT 
  COALESCE(TABLEA.COL1,TABLEB.COL1) AS COL1, 
  COALESCE(TABLEA.COL2,TABLEB.COL2) AS COL2, 
  TABLEA.COLN AS COLN_A, 
  TABLEB.COLN AS COLN_B, 
  (TABLEA.COLN-TABLEB.COLN) AS DIFF
FROM TABLEA
FULL OUTER JOIN TABLEB
  ON  TABLEA.COL1=TABLEB.COL1 
  AND TABLEA.COL2=TABLEB.COL2
;

The example is for 3 column tables. Anywhere where it has COL2 should be replaced with COL2, COL3, ... COLn-1 as suits the size of the tables. I see four places where this is necessary: the creation of TABLEA, the creation of TABLEB [note that these are identical, so the code only needs to generate the clause once and use it twice], the SELECT COALESCE(TABLEA.COLn,TABLEB.COLn) AS COLn and the ON clause AND TABLEA.COLn=TABLEB.COLn.

Obviously, you will also have to replace SCHEMA1.TAB1 and SCHEMA2.TAB2 as appropriate. The rest of the code should be generic and you don't need to use the actual column names, which should make the Java code much simpler.