Oracle Relational Division

2016-02-16

Relationship Division R÷s

Relationship mode R (x, y) S (y,z)

Meaning: Querying tuples in R that are related to all tuples in s

First, create the base table R and S

CREATE TABLER (XVARCHAR2(Ten), YVARCHAR2(Ten));CREATE TABLES (YVARCHAR2(Ten), ZVARCHAR2(Ten));INSERT  All intoRVALUES('X1','Y1') intoRVALUES('X2','Y2') intoRVALUES('X2','Y3') intoRVALUES('X2','Y1')SELECT 1  fromDUAL;INSERT  All intoSVALUES('Y1','Z1') intoSVALUES('Y2','Z3')SELECT 1  fromDUAL; fromR;

Second, decomposition

-- R SELECT *  from R

-- S SELECT *  from S

-- T SELECT  from S; CREATE TABLE  as SELECT  from S; SELECT *  from T

--WSELECTR.x, R.y from(SELECTY fromS) T Left JOINR onT.y=r.y;CREATE TABLEW as SELECTR.x, R.y from(SELECTY fromS) T Left JOINR onT.y=r.y;SELECT *  fromW

-- N SELECT COUNT (* from T; CREATE TABLE  as SELECT COUNT (* from T; SELECT *  from N

-- M SELECT COUNT (*fromGROUP by X; CREATE TABLE  as SELECT COUNT (*fromGROUP by X; SELECT *  from M

-- R÷s SELECT  from  Right JOIN  on = N.y_num;

Three, comprehensive

SELECTm.x from(SELECTXCOUNT(*) Y_num from(SELECTr.x, R.y from(SELECTY fromS) T Left JOINR onT.y=r.y) WGROUP  byX) M Right JOIN(SELECT COUNT(*) Y_num from(SELECTY fromS) T) N onM.y_num=N.y_num;

Oracle Relational Division