Skip to content
Home » JOIN Concepts in Relational Database Management System

JOIN Concepts in Relational Database Management System

    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

    In DBMS, JOIN is an important operation in relational algebra and it extracts 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 have a One-to-Many relationship.

    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 keys 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 results in a very large relation with multiple tuples. The JOIN operation accepts some conditions and applies them to 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.

    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 the 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

    References

    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.