SQL RIGHT OUTER JOIN in DB2 for i SQL

SQL RIGHT OUTER JOIN in DB2 for i SQL

Right Outer Join

A right outer join returns all the rows that an inner join returns plus one row for each of the other rows in the second table that do not have a match in the first table. It is same as LEFT OUTER JOIN with the tables specified in opposite order.

Syntax

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

Reference Tables for understanding RIGHT OUTER JOIN

Student Table

Course Table

Example using SQL RIGHT OUTER JOIN

Perform RIGHT 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 
RIGHt 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        
      -  -                       -        C3     EL        

The result of this query contains course id C3 that do not have a rollno in 1st table student. They are listed in the query, but have the null value returned for their Rollno, Name and Gender from the 1st table.