How SQL multiple Join Works in DB2 for i SQL

How SQL multiple Join Works

SQL Multiple Joins

A SQL query that contains the same or different join types, which are used more than once. Therefore, we can combine the multiple tables. So For Joining 3 tables let's say 2 joins are required i.e. one less than the number of tables.

Example using Multiple Joins

To understand using multiple joins let us first create some database tables:

Student table:

-- Create Table
CREATE TABLE Student (rollno integer, 
Name char(20),                        
subjectid integer)                    

--Insert Values
INSERT INTO Student VALUES
(1,'Ankur',201),          
(2,'Rahul',202),          
(3,'Raman',203),          
(4,'Vimal',204),
(5,'Samar',290)

--select * from student
ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202
     3   Raman                           203
     4   Vimal                           204
     5   Samar                           290

Subject table:

-- Create Table
CREATE TABLE Subject(subjectid Integer,   
SubjectName char(20),                     
SubjectTeacher char(20))                  

--Insert Values
INSERT INTO subject VALUES    
(201,'PHYSICS','Anupam'),     
(202,'CHEMISTRY','Nakul'),    
(203,'MATHS','Amit'),         
(204,'BIOLOGY','Mohan'),      
(205,'HINDI','Venkatesh'),    
(206,'ENGLISH','Venugopal'),  
(207,'SANSKRIT','Krishnakant')         

--select * from subject
SUBJECTID   SUBJECTNAME           SUBJECTTEACHER
      201   PHYSICS               Anupam        
      202   CHEMISTRY             Nakul         
      203   MATHS                 Amit          
      204   BIOLOGY               Mohan         
      205   HINDI                 Venkatesh     
      206   ENGLISH               Venugopal     
      207   SANSKRIT              Krishnakant   

Grade table:

-- Create Table
CREATE TABLE Grade(rollno Integer,
Subjectid integer,                
marks integer)                    

--Insert Values
INSERT INTO grade VALUES 
(1,201,50),              
(2,202,60),              
(3,203,70)                       

--select * from grade
ROLLNO       SUBJECTID           MARKS 
     1             201              50 
     2             202              60 
     3             203              70   

Join multiple tables using INNER JOIN

Example: Get the Student name, along with their subject name, Subject teacher and grade.

SELECT s.name, su.SubjectName, su.SubjectTeacher, g.marks  
from                                                       
student s                                                  
INNER JOIN subject su                                      
on S.subjectid  = su.subjectid                             
INNER JOIN grade  g                                        
on s.rollno = g.rollno                                     

NAME                  SUBJECTNAME           SUBJECTTEACHER                MARKS
Ankur                 PHYSICS               Anupam                           50
Rahul                 CHEMISTRY             Nakul                            60
Raman                 MATHS                 Amit                             70

Explanation:

At first the INNER JOIN between STUDENT and SUBJECT table will derive the matched rows between these two tables and formed a virtual table.

Now, this formed virtual table gets combined with GRADE table using second INNER JOIN to derive the matched rows between the previous result se i.e. virtual table with the grade table.

Join multiple tables using LEFT JOIN

Example: Get the names of all the student even if they did not get assigned to any subject or did not get any marks in the assigned subject.

SELECT s.name, su.SubjectName, su.SubjectTeacher, g.marks  
from                                                       
student s                                                  
LEFT JOIN subject su                                       
on S.subjectid  = su.subjectid                             
LEFT JOIN grade  g                                         
on s.rollno = g.rollno                                     

NAME                  SUBJECTNAME           SUBJECTTEACHER                MARKS 
Ankur                 PHYSICS               Anupam                           50 
Rahul                 CHEMISTRY             Nakul                            60 
Raman                 MATHS                 Amit                             70 
Vimal                 BIOLOGY               Mohan                              -
Samar                 -                     -                                  -

Explanation:

At first the LEFT JOIN between STUDENT and SUBJECT table will derive all the rows from left table i.e. STUDENT table even if they are not present in right table i.e. subject table and set the NULL values to column SubjectName and SubjectTeacher for subjectIds not available in the right subject table and creates a virtual table.

Now, this formed virtual table gets combined with GRADE table using second LEFT JOIN to derive all the rows from left derived virtual table and assigned marks column from right table to NULL if any rollno is not present in grade table w.r.t the derived virtual table in left.

Join multiple tables using both INNER JOIN and LEFT JOIN

Example: Get the names of all the student even if they did not get any marks in the assigned subject.

SELECT s.name, su.SubjectName, su.SubjectTeacher, g.marks  
from                                                       
student s                                                  
INNER JOIN subject su                                       
on S.subjectid  = su.subjectid                             
LEFT JOIN grade  g                                         
on s.rollno = g.rollno                                     

NAME                  SUBJECTNAME           SUBJECTTEACHER                MARKS  
Ankur                 PHYSICS               Anupam                           50  
Rahul                 CHEMISTRY             Nakul                            60  
Raman                 MATHS                 Amit                             70  
Vimal                 BIOLOGY               Mohan                              - 

Explanation:

At first the INNER JOIN between STUDENT and SUBJECT table will derive the matched rows between these two tables and formed a virtual table.

Now, this formed virtual table gets combined with GRADE table using second LEFT JOIN to derive all the rows from left derived virtual table and assigned marks column from right table to NULL if any rollno is not present in grade table w.r.t the derived virtual table in left.