sunny - 8 months ago 42

SQL Question

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

Source (Stackoverflow)