Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: left join acts like inner join? (MS SQL)

    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
    Last edited by magic3000; 04-29-11 at 00:20.

  2. #2
    Join Date
    Apr 2011
    Posts
    2
    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'

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    SELECT ug.UserSystem, ug.UserGroupCode, uag.UserCode
    FROM   UserGroup AS ug LEFT OUTER JOIN
           ( SELECT UserCode
             FROM UserAssignedGroup
             WHERE UserCode = 'test' ) AS uag ON ( ug.UserGroupCode = uag.UserGroupCode )
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by stolze View Post
    However, I wouldn't use this predicate as join predicate because it does not involve both tables.
    Why not?
    It's a perfectly acceptable way to limit the number of joined rows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •