hi...

i am new on left join. I don't really know the reason or concept of the problem that I found.

In following SQL, I want to retrieve all user group and tell me which group is assigned to a specific user. I have 2 user groups in "UserGroup" and 1 of the user group assigned to a user in "UserAssignedGroup".

First, I used this SQL. But it only return only 1 user group
select ug.UserSystem, ug.UserGroupCode, uag.UserCode
from UserGroup ug left join UserAssignedGroup uag on ug.UserGroupCode = uag.UserGroupCode
where uag.UserCode = 'test'


If I change to use sub-query then it return 2 user groups that I want.
select ug.UserSystem, ug.UserGroupCode, x.UserCode
from mp_UserGroup ug left join (select uag.UserSystem, uag.UserGroupCode, uag.UserCode from mp_UserAssignedGroup uag where uag.UserCode = 'test') x on x.UserGroupCode = ug.UserGroupCode

I am not sure the problem on the criteria of UserCode that limits the result set. Hope to see someone can share the knowledge with me.

Thank you

yes..

that must be the criteria problem. It WORKS, when i put the usercode criteria inside the join statement!!! cheers

select ug.UserSystem, ug.UserGroupCode, uag.UserCode
from UserGroup ug left join UserAssignedGroup uag on ug.UserGroupCode = uag.UserGroupCode and uag.UserCode = 'test'

By putting the condigion usercode= 'test' into the where clause you are effectivily turning the outer join into an inner join. The result is evaluated like this:

First all rows are retrieved leaving values from the outer joined table as null
Then the where condition is applied, but as none of the null values that stem from the outer join meet that condition those rows are discarded.

As you already noticed: you need to move the condition into the JOIN clause.

In your example, uag.UserCode = 'test' is NOT a join predicated but rather a predicate that is evaluation on the already joined table. Thus, all things related to evaluating NULLs in predicates apply here. If you move that predicate to a sub-select or use it as join predicate, the semantics are as you expect.

However, I wouldn't use this predicate as join predicate because it does not involve both tables. Instead, I'd do something like this to describe more clearly what the statement tries to accomplish:
The selection predicate is now moved to the table on which you want to filter. Every half-way serious (rule-based) optimizer should be able to figure this out by itself - but it can only do that if it wouldn't change the semantics.

Why not?
It's a perfectly acceptable way to limit the number of joined rows