Left Outer Join Issue

This is a post in continuation of my previous post where I had described an issue I had faced with Outer join. And the solution that I had mentioned at the end of it was not correct. In fact the query that I had put in was also incomplete. Following line describes the issue and the solution. Consider two tables

Employee - 

Employee (Number) - (10 distinct records)
Department (Char)

Manager -

Employee Number (Char) - (3 records matching with TableA)
Employee Name(Char)
Manager (char)

Requirement:- Select the list of all employee numbers,Employee Name. For normal employees, employee name can be blanks while for managers select the correct name from Manager table.

I wrote the following query to achieve the same using a Left Outer Join.

Select A.EmpNo,ifnull(B.EmpName,' ') from Employee A Left Outer Join

Manager B On A.EmpNo = B.EmpNo Where B.IsManager = 'Y'

This query should ideally return 10 records since a Left Outer Join is done. But here
the query would return only 8 records i.e. it would function like an Inner Join.

Why:- Here the where condition is applied on the Joined table. Since for the mis- matching rows the value is null, the where condition would fail hence that row would be omitted. Here is an alternate solution

Select A.EmpNo,ifnull(B.EmpName,' ') from Employee A Left Outer Join

(Select * From Manager Where IsManager = 'Y') B On A.EmpNo = B.EmpNo

This would work as there is no where condition applied on the null columns.

Thanks Lynne Roll and all other System i Network users for this piece of learning.

Click here to go to the System i Network thread. ; Forum Post