Relational Division

Relational division normally requires two not exists sub queries used in three different tables. Below sql snippet shows how division could be employed when all the data is in a single table.

 PROJ  SUBJ
----- -----
    1     1
    1     2
    1     3
    2     3
    2     1
    3     1
    3     2
    3     3
    4     1
    5     2
    6     3                    

Hypothetical situation is that, there are several projects and each project uses number of different subjects. Idea is to find projects that are uses all the subjects.

The theory


theory implementation

select distinct proj from x where proj not in 
(select distinct proj from (select * from 
(select distinct proj from x) t1, 
(select distinct subj from x)  t2) t3
where (proj,subj) not in (select proj,subj from x));

Using With clause in Oracle

with t1 as (select DISTINCT proj from x),
t2 as (select distinct subj from x)
select distinct proj from x where proj not in 
( select proj from t1,t2 where (proj,subj) not in (select * from x));

Using not exists sub queries

select * from (select distinct proj from x) projects
where not exists(
  select * from (select distinct subj from x) subject
  where not exists
    (select * from x
      where x.proj = projects.proj
      and x.subj = subject.subj
      ));