sunny sunny - 1 month ago 9
SQL Question

minus a set of some constant values from a set of values of a column in oracle

there is a table like id- name-roll
i have some set of constant given value of roll like-
(1,2,6,8,5) let it be set A
table has roll like(1,2,3,4,5,6,7) let it be set B
i want to know A-B
means those values of roll which is not in the table but exist in the given constant set A

i tried
select roll from "tablename" where roll not in(1,2,6,8,5);
but it return just opposite-
it return B-A
but i want A-B
please help
i also tried

select (1,2,6,8,5) from dual minus select roll from tablename;
but it gives some syntex error

Answer

( select 1 as id from dual union all select 2 from dual union all select 6 from dual union all select 8 from dual union all select 5 from dual ) minus Select id from b