DB2 LEFT OUTER JOIN

A LEFT OUTER JOIN retrieves all rows from one table (often called the "left" table) and any matching rows from another table (the "right" table). If there's no match in the right table, the result will still include all rows from the left table, with NULL values for columns of the right table.

This is helpful when we want to see all data from the main (left) table, regardless of whether there's related data in the second (right) table. For example, if we want a list of all employees along with their department names (showing NULL if an employee isn't assigned to a department), a LEFT OUTER JOIN is ideal.

Syntax -

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

• columns: Specifies which columns to retrieve from each table.
• table1: The main (left) table in the LEFT OUTER JOIN.
• table2: The second (right) table in the LEFT 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 LEFT OUTER JOIN, we can combine the EMPLOYEE and DEPARTMENT tables to create a result showing each employee's department name, including NULL where the employee has no department.

EMPLOYEE Table

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

DEPARTMENT Table

DEPT_ID	DEPT_NAME
1	Human Resources
2	Finance
3	IT

Query -

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

Result Table

EMP_ID	EMP_NAME	DEPT_NAME
101	Alice	Human Resources
102	Bob	Finance
103	Charlie	NULL
104	David	Human Resources
105	Eva	IT

---