SQL Query to Return Differences Between Two Tables

sql query to return differences between two tables

IF you have tables A and B, both with colum C, here are the records, which are present in table A but not in B:

SELECT A.*
FROM A
    LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL

To get all the differences with a single query, a full join must be used, like this:

SELECT A.*, B.*
FROM A
    FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL

What you need to know in this case is, that when a record can be found in A, but not in B, than the columns which come from B will be NULL, and similarly for those, which are present in B and not in A, the columns from A will be null.

Compare (estimated) query/solution perfomance. Return differences between two tables (heaps)

You should test on your data.

But absent other information, I would expect the first to be better. Each is doing three set operations on the entire table. In the first, the "intermediate" tables are smaller, so I would expect better performance.

That is, UNION/UNION ALL is going to create a bigger table (in the second solution) which then needs to be processed.

Note that in SQL Server, EXCEPT and INTERSECT also remove duplicates, which might add additional overhead.

SQL Server how to compare 2 tables to extract differences

Would the EXCEPT operator fix your issue?

SELECT A.a, A.b, ... 
FROM Table A  
EXCEPT  
SELECT B.a, B.b, ... 
FROM Table B;  

Doc here: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15

How to find difference between the values of two tables in sql?

Maybe you're after a simple conditional case to pivot your values, you can then simply subtract:

select productname, 
Coalesce(Sum(case when TransactionType='sell' then productvalue end),0) -
Coalesce(Sum(case when TransactionType='buy' then productvalue end),0) ProfitLoss
from t
group by productname

Example Fiddle

How to compare if two tables got the same content?

Use EXISTS

    Select case when count(*) >0 
     Then
    'Common content exists' 
     Else 
     'Nothing in common' 
     End
    from table1 t1 where
   EXISTS (select 1 from table2 t2 where 
    t2.id=t1.id )

SQL Query to Join Two Tables where data in one table is transposed

You could use CTEs to split up the impact from outcome. Also, your screenshot had an error for task_id 6.

with t_impact as (
  select task_id, value
  from table2
  where name = 'task_impact'
  ), 
t_outcome as (
  select task_id, value
  from table2
  where name = 'task_outcome'
  )
select distinct t1.id, 
  t1.title, 
  i.value as task_impact, 
  o.value as task_outcome
from table1 t1
left join t_impact i
  on t1.id = i.task_id
left join t_outcome o
  on t1.id = o.task_id
order by t1.id

DB-fiddle found here.

---

Related Topics