RDBMS – Core Joins

This document will introduce the relational algebra operations of RDBMS- Core Joins, which include UNION, INTERSECT，MINUS，Outer Join（+），Self-Join 。

1.Create the test table

CREATE TABLE xx408779_departments

 (depID  NUMBER(38,0),

 depName VARCHAR2(20),

 delFlag NUMBER(1,0)

);

CREATE TABLE xx408779_employees

 ( empID  NUMBER(38,0),

 empName VARCHAR2(20),

 depID  NUMBER(38,0),

 delFlag NUMBER(1,0)

 );

INSERT INTO xx408779_departments VALUES(1,'Finacle',0);

INSERT INTO xx408779_departments VALUES(2,'Marketing',0);

INSERT INTO xx408779_departments VALUES(3,'HR',1);

INSERT INTO xx408779_departments VALUES(4,'IT',0);

INSERT INTO xx408779_departments VALUES(7,'Manufacturer',0);

INSERT INTO xx408779_employees VALUES(1,'wbq',1,0);

INSERT INTO xx408779_employees VALUES(2,'czh',2,0);

INSERT INTO xx408779_employees VALUES(3,'chh',1,0);

INSERT INTO xx408779_employees VALUES(4,'wal',2,0);

INSERT INTO xx408779_employees VALUES(5,'ddd',3,0);

INSERT INTO xx408779_employees VALUES(6,'wdfg',5,0);

 

2.Retrieval using UNION

SELECTd.depID,d.depName,E.empName

 FROMxx408779_departmentsd,xx408779_employeesE

 WHEREd.depID(+)=E.depID

UNION

SELECTd.depID,d.depName,E.empName

 FROMxx408779_departmentsd,xx408779_employeesE

 WHEREd.depID=E.depID(+)

 

3. Retrieval using  INTERSECT

SELECTd.depID,d.depName,E.empName

 FROMxx408779_departmentsd,xx408779_employeesE

 WHEREd.depID(+)=E.depID

INTERSECT

SELECTd.depID,d.depName,E.empName

 FROMxx408779_departmentsd,xx408779_employeesE

 WHEREd.depID=E.depID(+)

 

4. Retrieval using Minus

SELECTd.depID,d.depName,E.empName

 FROMxx408779_departmentsd,xx408779_employeesE

 WHEREd.depID(+)=E.depID

MINUS

SELECTd.depID,d.depName,E.empName

 FROMxx408779_departmentsd,xx408779_employeesE

 WHEREd.depID=E.depID(+)

 

5.Retrieval from Multiple tables - Outer join

Example of left outer join

SELECTd.depID,d.depName,E.empName

 FROMxx408779_departmentsd,xx408779_employeesE

 WHEREd.depID=E.depID(+)

Example of right outer join

SELECTd.depID,d.depName,E.empName

 FROMxx408779_departmentsd,xx408779_employeesE

 WHEREd.depID(+)=E.depID