Question 89853 in PostgreSQL Question posted in
The official documentation can be found here.

How to find difference between table with multiple conditions – Postgresql

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)

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search