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));
CREATE TABLE STUDENT ( SID NUMBER(3) PRIMARY KEY, SNAME VARCHAR2(30), TNAME VARCHAR2(25), GRADE CHAR);
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.
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.
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.
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.
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.
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.
