SO_Minus_NotExists

View All Public Scripts Login to Run Script

Script Name SO_Minus_NotExists
Visibility Unlisted - anyone with the share link can access
Description StackOverflow demo of exclusive set operation
Area SQL General / SQL Query
Contributor Andrew Clarke
Created Wednesday December 27, 2017

Statement 1

create table planPlanSet (planname varchar2(10), setname varchar2(10))

Table created.

Statement 2

insert into  planPlanSet values ('Plan1','Planset1')

1 row(s) inserted.

Statement 3

insert into  planPlanSet values ('Plan2','Planset2')

1 row(s) inserted.

Statement 4

insert into  planPlanSet values ('Plan3','Planset3')

1 row(s) inserted.

Statement 5

insert into  planPlanSet values ('Plan4','Planset1')

1 row(s) inserted.

Statement 6

insert into  planPlanSet values ('Plan5','Planset5')

1 row(s) inserted.

Statement 7

insert into  planPlanSet values ('Plan1','Planset4')

1 row(s) inserted.

Statement 8

insert into  planPlanSet values ('Plan2','Planset4')

1 row(s) inserted.

Statement 9

create table ExcludedPlans (planname varchar2(10))

Table created.

Statement 10

insert into  ExcludedPlans values ('Plan1')

1 row(s) inserted.

Statement 11

insert into  ExcludedPlans values ('Plan2')

1 row(s) inserted.

Statement 12

select * from planPlanSet pss 
where not exists 
  ( select planname from planPlanSet x 
    where x.setname = pss.setname 
    minus 
    select planname from ExcludedPlans)


PLANNAME SETNAME
Plan2 Planset2
Plan1 Planset4
Plan2 Planset4

3 rows selected.

PLANNAME	SETNAME
Plan2	Planset2
Plan1	Planset4
Plan2	Planset4

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database