SQL LEFT OUTER JOIN in DB2 for i SQL

SQL LEFT OUTER JOIN in DB2 for i SQL

Left Outer Join

A left outer join returns all the rows that an inner join returns plus one row for each of the other rows in the first table that do not have a match in the second table.

Syntax

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

Reference Tables for understanding LEFT OUTER JOIN

Student Table

Course Table

Example using SQL LEFT OUTER JOIN

Perform LEFT 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 
LEFT 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        -      -         

The result of this query contains some Rollno that do not have a CourseId. They are listed in the query, but have the null value returned for their CourseId and CourseName from the 2nd table.