JOIN concepts in Relational Database Management System

In DBMS, JOIN is a important operation in relational algebra and it extract useful information from joining two or more relations. In this lab, you will create two tables TEACHER and STUDENT. A STUDENT studies under one or more TEACHER for different subjects, so student and teacher has One-to-Many relationship.

JOIN put condition (selections and projections)  on a CROSS-PRODUCT from two or more tables and the result is a smaller relation than the CROSS-PRODUCT

Step 1: Create Table TEACHER and STUDENT.

Before we dive into the JOIN concept let’s create relations – TEACHER and STUDENT. You have to use the following SQL commands to do that. In the relations, TEACHER and STUDENT, the TID and SID are primary key respectively.

CREATE TABLE T (TID NUMBER(5) PRIMARY KEY , CNAME VARCHAR2(30));

 

Figure 1: Create Teacher Relation
Figure 1: Create Teacher Relation

 

CREATE TABLE STUDENT ( SID NUMBER(3) PRIMARY KEY, SNAME VARCHAR2(30), TNAME VARCHAR2(25), GRADE CHAR);

 

Figure 2: Create Relation STUDENT
Figure 2: Create Relation STUDENT

Step 2: Insert values into both the tables as follows

The next step is to insert values into both relations. First, you need to enter values for the TEACHER relation using following command,

INSERT INTO TEACHER VALUES(10001,'RAMESH');   INSERT INTO TEACHER VALUES(10002,'KIRAN');   INSERT INTO TEACHER VALUES(10003,'JOHN');   INSERT INTO TEACHER VALUES(10004,'PETER');   INSERT INTO TEACHER VALUES(10005,'FRODO');

An instance of TEACHER relation is given below.

Figure 3: Instance of Relation TEACHER
Figure 3: Instance of Relation TEACHER

Insert values into the STUDENT table as follows.

INSERT INTO STUDENT VALUES(501,'ANIL KUMAR' ,'RAMESH','S');   INSERT INTO STUDENT VALUES(502,'RAJESH KAPOOR','KIRAN','D');   INSERT INTO STUDENT VALUES(503,'SUBBARAJ','JOHN','A');   INSERT INTO STUDENT VALUES(504,'NAGESH ','PETER','C');   INSERT INTO STUDENT VALUES(505,'RAM PRASAD','FRODO','A');   INSERT INTO STUDENT VALUES(506, 'JASSE');   INSERT INTO STUDENT VALUES(507,'MADHU');

An instance of relation STUDENT is shown below, you must get similar results. For each student there is a teacher associated who taught a course.

The course relation is not required at this moment because these two relations are sufficient to understand the JOIN concepts in DBMS.

Figure 4: An Instance of STUDENT relation
Figure 4: An Instance of STUDENT relation

CROSS-PRODUCT (CARTESIAN PRODUCT)

A Cross-Product is a product of two or more relations and is denoted by T ⨯ S. Suppose the relation Teacher has 5 tuples and the relation Student has 4 tuples, then the Cross-Product will have 5 x 4 = 20 tuples. Cross-Product is a JOIN without any conditions.

The Cross-Product operation result in a very large relation with multiple tuples.The JOIN operation accepts some conditions and apply them on the Cross-Product and result is a relational instance you want.

Depending on the condition applied you get different types of JOIN.

Note: A Cross-Product is also called NATURAL JOIN.

EQUI-JOIN

SELECT * FROM TEACHER T, STUDENT D WHERE T.TNAME = D.TNAME;

This will return all the rows that are common in both teacher and student table.
i.e. t.tname = d.tname.

Advertisements


Figure 5: Equi-Join on relation TEACHER and STUDENT
Figure 5: Equi-Join on relation TEACHER and STUDENT

LEFT-OUTER JOIN

In this join type , you get all rows that are common to both tables, (EQUI-JOIN) + remaining row from left side table.

for example

SELECT T.TID,T.TNAME,S.SNAME, S.GRADE FROM TEACHER LEFT OUTER JOIN STUDENT ON T.TNAME = S.TNAME;

In the above command the TEACHER is left table and student is right table.

Figure 6: Left-Outer JOIN
Figure 6: Left-Outer JOIN

RIGHT-OUTER JOIN

In this type of JOIN, you get all row that are common to both table(EQUI-JOIN) + REMAINING ROWS from right side table.

SELECT S.SID, S.SNAME, T.TNAME FROM TEACHER T RIGHT OUTER JOIN STUDENT S ON T.TNAME = S.TNAME;

The student table is right hand side table and you get most of  your column from right hand side table. It means that the query returns rows that are in both table + rows in student table only.

Figure 7: Right - Outer JOIN
Figure 7: Right – Outer JOIN

 


Bibliography

Avi Silberschatz, Henry F. Korth, and S. Sudarshan. 27-Jan-2010. Database System Concepts. McGraw-Hill Education.

Ramakrishnan, Johannes Gehrke and Raghu. 1996. Database Management Systems. McGraw Hill Education; Third edition (1 July 2014).

Wikipedia. n.d. Data definition language. Accessed March 14, 2018. https://en.wikipedia.org/wiki/Data_definition_language.

 

Advertisements