DB2 RIGHT OUTER JOIN

A RIGHT OUTER JOIN is used to retrieve all rows from the second (right) table and any matching rows from the first (left) table. If there’s no match in the left table, the result will still include all rows from the right table, with NULL values for columns from the left table. This is helpful when we want to see all data from the second table, regardless of whether there’s related data in the first table.

For example, if we want a list of all departments along with their employees (showing NULL if a department doesn’t have employees), a RIGHT OUTER JOIN is suitable.

Syntax -

SELECT columns
  FROM table1
       RIGHT OUTER JOIN table2 
	ON table1.column = table2.column
 WHERE condition;

• columns: Specifies which columns to retrieve from each table.
• table1: The first (left) table in the RIGHT OUTER JOIN.
• table2: The main (right) table in the RIGHT OUTER JOIN.
• ON: Defines the join condition, specifying which columns to match between the tables.
• WHERE: Optional condition to filter rows further after joining.

Examples -

Scenario - Using a RIGHT OUTER JOIN, we can combine the EMPLOYEE and DEPARTMENT tables to create a result showing each department’s employee names, including NULL where the department has no employees.

EMPLOYEE Table

EMP_ID	EMP_NAME	DEPT_ID
101	Alice	1
102	Bob	2
103	Charlie	NULL
104	David	1

DEPARTMENT Table

DEPT_ID	DEPT_NAME
1	Human Resources
2	Finance
3	IT

SELECT EMPLOYEE.EMP_ID, 
        EMPLOYEE.EMP_NAME, 
		DEPARTMENT.DEPT_NAME
   FROM EMPLOYEE
          RIGHT OUTER JOIN DEPARTMENT 
	 ON EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID;

Result Table

EMP_ID	EMP_NAME	DEPT_NAME
101	Alice	Human Resources
104	David	Human Resources
102	Bob	Finance
NULL	NULL	IT

---