SQL FULL OUTER JOIN in DB2 for i SQL

SQL FULL OUTER JOIN in DB2 for i SQL

Full Outer Join

A full outer join returns matching rows from both tables along with nonmatching rows from both tables.

Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name

Reference Tables for understanding FULL OUTER JOIN

Student Table

Course Table

Example using SQL FULL OUTER JOIN

Perform FULL OUTER JOIN on Table STUDENT and COURSE based on field ROLLNO

SELECT S.ROLLNO, S.NAME, S.GENDER, C.COURSEID, C.COURSENAME FROM
student s 
FULL OUTER JOIN course c ON s.rollno = c.rollno       

Result:

ROLLNO   NAME                  GENDER  COURSEID  COURSENAME
     1   AMIT                    M        C1     IT        
     2   AMAN                    M        C2     EC        
     3   HEMANT                  M        -      -         
     4   ANNAMALAI               M        -      -         
     5   KESHAV                  M        -      -         
      -  -                       -        C3     EL        

The result of this query contains roll no 1, 2, 3, 4, 5 from first table STUDENT.

roll no 1 and 2 are matching rows in first and second table i.e. student and course table so all column values returned.

roll no 3,4 and 5 are not present in second table course, therefore their courseid and coursename is returned as NULL.

Course id c3, whose roll no.5 in the second table course is returned and is not present in first table student and we selected s.rollno i.e. rollno coming from first table student therefore, rollno, name and Gender are NULL returned for this course id.