How to find difference between table with multiple conditions

I have exact two tables but some value differences. So I would like to find those differences with condition that if the column value has a difference of more than 10.

For example, all 9 columns have the same values in both tables, but the difference between the values column is 11, so this record is different. If the value difference is 9 so records are the same.

So I wrote this query to get differences:

select * 
from  test.test m 
inner join test.test1 t 
    on
m.month_date = t.month_date and  
m.level_1 = t.level_1 and
m.level_2 = t.level_2 and 
m.level_3 = t.level_3 and 
m.level_4 = t.level_4 and 
m.level_header = t.level_header and 
m.unit = t.unit and 
m.model_type_id = t.model_type_id and 
m.model_version_desc = t.model_version_desc 


where m.month_date = '2022-11-01' and abs(m.value - t.value)  > 10)

so this returns me all records that all column values are matched but did not pass the value difference condition.

Second, i have full outer join to get all differences

select  *
from  test.test m 
full outer join test.test1 t 
    on
m.month_date = t.month_date and  
m.level_1 = t.level_1 and
m.level_2 = t.level_2 and 
m.level_3 = t.level_3 and 
m.level_4 = t.level_4 and 
m.level_header = t.level_header and 
m.unit = t.unit and 
m.model_type_id = t.model_type_id and 
m.model_version_desc = t.model_version_desc 


where m.month_date is null  or t.month_date is null and  m.month_date = '2022-11-01'

How can I combine the results of these two queries without UNION? I want to have only one query (sub query is acceptable)

In your first query, you can replace the null values for a specific number. Something like this:

    where m.month_date = '2022-11-01' and abs(ISNULL(m.value,-99) - ISNULL(t.value,-99))  > 10)

The above will replace the nulls for -99 (choose an appropriate value for your data), so if you have that m.value is 10 and t.value is null, then should be returned in your first query.

Assuming that for a given day, you need to find

• rows that match between the tables but exceed the value difference threshold

  AND

• rows present in either left or right table, that don't have a corresponding row in the other table

select  *
from  test.test m 
    full outer join test.test1 t 
        using (
         month_date,
         level_1,
         level_2, 
         level_3, 
         level_4, 
         level_header, 
         unit, 
         model_type_id, 
         model_version_desc )
where (m.month_date is null
       or    t.month_date is null
       and   m.month_date = '2022-11-01'  )
or    (m.month_date = '2022-11-01' and abs(m.value - t.value)  > 10);

Online demo

Since the columns used to join the tables have the same names, you can shorten their list by swapping out the lengthy table1.column1=table2.column1 and... list of pairs for a single USING (month_date,level_1,level_2,level_3,...) (doc). As a bonus, it will avoid listing the matching columns twice in your output, once for the left table, once for the right table.

select * 
from (select 1,2,3) as t1(a,b,c)
    full outer join 
     (select 1,2,3) as t2(a,b,c)
        on t1.a=t2.a 
        and t1.b=t2.b 
        and t1.c=t2.c;
-- a | b | c | a | b | c
-----+---+---+---+---+---
-- 1 | 2 | 3 | 1 | 2 | 3

select * 
from (select 1,2,3) as t1(a,b,c)
    full outer join 
     (select 1,2,3) as t2(a,b,c)
        using(a,b,c);
-- a | b | c
-----+---+---
-- 1 | 2 | 3