MINUS vs EXCEPT | MINUS ALL vs EXCEPT ALL ORACLE 21C

 

 

 

 

 

drop table t_test;

create table t_test(empid number,dept varchar2(100),sal number);

insert into t_test values(1001,'HR',20000);

insert into t_test values(1002,'SALES',30000);

insert into t_test values(1003,'IT',40000);

insert into t_test values(1004,'PURCHASE',20000);

insert into t_test values(1005,'IT',30000);

 

select sum(case when dept='IT' THEN sal end) IT_TOTAL,

sum(case when dept='HR' THEN sal end) HR_TOTAL,

sum(case when dept NOT IN('IT','HR') THEN sal end) OTHER_TOTAL

FROM T_TEST;