How to Select the Comparison of Two Columns as One Column in Oracle

How to select the comparison of two columns as one column in Oracle

If you want to consider null values equality too, try the following

select column1, column2, 
   case
      when column1 is NULL and column2 is NULL then 'true'  
      when column1=column2 then 'true' 
      else 'false' 
   end 
from table;

Comparing values in two columns in same table and creating new column

This is a little tricky because when there is only one row, then you want to keep the original ordering. One method is aggregation with union all:

select least(sender, receiver) as sender, greatest(sender, receiver) as receiver, 1 as exchanged
from t
group by least(sender, receiver), greatest(sender, receiver)
having count(*) = 2
union all
select sender, receiver, 0
from t
where not exists (select 1
                  from t t2
                  where t2.receiver = t.sender and t2.sender = t2.receiver
                 );

SQL query to compare multiple columns in same table in oracle

You can use MINUS.

if no_data then both are the same, if there are some records - mean that there is a difference between

create table emp as select * from hr.employees;

insert into emp select employee_id+1000, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, 
 decode(department_id ,30,70, department_id)
 from hr.employees;

select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id  
from emp where employee_id <= 1000
minus
select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id  
from emp where employee_id > 1000;

But you have to list all columns, because if you have eg different dates or ids - they will be compared too. But it's easier to list columns in SELECT clause then write for everyone WHERE condition.
Maybe it will help.

-- or if different tables and want to compare all cols simply do

drop table emp;
create table emp as select * from hr.employees;
    
create table emp2  as 
select employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, 
 decode(department_id ,30,70, department_id) department_id
 from hr.employees;

select * from emp
minus 
select * from emp2;

---- ADD DATE CRITERIA

-- yes, you can add date criteria and using analytical functions check which
-- is newer and which is

older and then compare one to another. like below:

drop table emp;
create table emp as select * from hr.employees;
        
insert into emp 
        select
           employee_id, 
           first_name, 
           last_name, 
           email, 
           phone_number, 
           hire_date+1, 
           job_id, 
           salary, 
           commission_pct, 
           manager_id, 
           decode(department_id ,30,70, department_id)
        from hr.employees;
        
     with data as   --- thanks to WITH you retrieve data only once
            (select employee_id, first_name, last_name, email, phone_number, 
                hire_date, 
                row_number() over(partition by employee_id order by hire_date desc) rn  -- distinguish newer and older record, 
               job_id, salary, commission_pct, manager_id, department_id  
        from emp)
        select employee_id, first_name, last_name, email, phone_number, department_id from data where rn  = 1
        MIUNUS--- find the differences
        select employee_id, first_name, last_name, email, phone_number, department_id from data where rn = 2;

---

Related Topics