Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Question Unanswered: Right join with where clause returns wrong results

    How do we specify 'where' clause in right join ?

    This does not work

    Code:
    select a.*,b.* from table1 a RIGHT OUTER JOIN table2 b on a.id=b.id WHERE b.STATUS=1 ORDER BY a.ID DESC;
    This returns all columns of table1 as nulls and only matched records from table2.
    How do I list all columns of table1 present in table2, plus additional columns in table2 not existing in table1 matching the where condition ?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand your requirements.

    Is it rows that you wrote columns?
    How do I list all columns of table1 present in table2, plus additional columns in table2 not existing in table1 matching the where condition ?
    Please show examples. roes in table1 and table2, and your expected result.

  3. #3
    Join Date
    Mar 2009
    Posts
    73
    Good day Tonkuma, thanks for the reply.

    I want all results in table 1 existing in table 2 and all results of table 2 who do not exist in table1 where Status='Active'

    Table1
    ----------
    ID Name
    ----------
    1 Peter
    2 James
    3 Mark
    4 Paul

    Table2
    --------------------------------
    DEPTCode DEPTName ID Status
    ---------------------------------
    4 Sales 10 Active
    5 HR 1 Active
    6 Logistics 2 Active
    7 Marketing 3 Disabled
    8 IS 4 Active

    Result
    -----------------------------------------
    ID Name DeptCode DeptName ID Status
    -----------------------------------------
    1 Peter 5 HR 1 Active
    2 James 6 Logistics 2 Active
    4 Paul 8 IS 4 Active
    - - 4 Sales 10 Active

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If your required result is what you showed, your first query would be an answer.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH Table1(ID , Name) AS (
    VALUES
      (1 , 'Peter')
    , (2 , 'James')
    , (3 , 'Mark' )
    , (4 , 'Paul' )
    )
    , Table2(DEPTCode , DEPTName , ID , Status) AS (
    VALUES
      (4 , 'Sales'     , 10 , 'Active'  )
    , (5 , 'HR'        ,  1 , 'Active'  )
    , (6 , 'Logistics' ,  2 , 'Active'  )
    , (7 , 'Marketing' ,  3 , 'Disabled')
    , (8 , 'IS'        ,  4 , 'Active'  )
    )
    SELECT t1.* , t2.*
     FROM  table1 t1
     RIGHT OUTER JOIN
           table2 t2
       ON  t2.id = t1.id
     WHERE t2.status = 'Active'
    ;
    ------------------------------------------------------------------------------
    
    ID          NAME  DEPTCODE    DEPTNAME  ID          STATUS  
    ----------- ----- ----------- --------- ----------- --------
              1 Peter           5 HR                  1 Active  
              2 James           6 Logistics           2 Active  
              4 Paul            8 IS                  4 Active  
              - -               4 Sales              10 Active  
    
      4 record(s) selected.
    But, the result does not satisfy "all results in table 1 existing in table 2" which should include the following row in my understandings.
    3 Mark 7 Marketing 3 Disabled

    The following query includes the row:
    Code:
    SELECT t1.* , t2.*
     FROM  table1 t1
     RIGHT OUTER JOIN
           table2 t2
       ON  t2.id = t1.id
     WHERE t1.id IS NOT NULL
       OR  t2.status = 'Active'
     ORDER BY
           t1.id
         , t2.id
    ;
    ------------------------------------------------------------------------------
    
    ID          NAME  DEPTCODE    DEPTNAME  ID          STATUS  
    ----------- ----- ----------- --------- ----------- --------
              1 Peter           5 HR                  1 Active  
              2 James           6 Logistics           2 Active  
              3 Mark            7 Marketing           3 Disabled
              4 Paul            8 IS                  4 Active  
              - -               4 Sales              10 Active  
    
      5 record(s) selected.
    Last edited by tonkuma; 07-16-10 at 06:38. Reason: Added t1.id to ORDER BY clause.

  5. #5
    Join Date
    Mar 2009
    Posts
    73
    You are absolutely right there, I missed that 'or & IS NOT NULL' part.
    Couple of questions
    1) Why have you used 'OR' and 'IS NOT NULL' ? What is its purpose ?
    2) Can we do it inside ON like
    ' ON t2.id = t1.id
    AND t2.status = 'Active' '
    Does 'ON' not gets executed before the where clause. But this returns wrong results anyway
    3) You are awesome, as always =)

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) Why have you used 'OR' and 'IS NOT NULL' ? What is its purpose ?
    A1) Without 'OR' and 'IS NOT NULL', the row with status 'Disabled' in table2 will be excluded from the result.

    Like this example:
    (a row " 3 Mark 7 Marketing 3 Disabled" was not in the result.)
    Code:
    ...
       ON  t2.id = t1.id
     WHERE t2.status = 'Active'
    ;
    ------------------------------------------------------------------------------
    
    ID          NAME  DEPTCODE    DEPTNAME  ID          STATUS  
    ----------- ----- ----------- --------- ----------- --------
              1 Peter           5 HR                  1 Active  
              2 James           6 Logistics           2 Active  
              4 Paul            8 IS                  4 Active  
              - -               4 Sales              10 Active
    Reading your requirements:
    I want all results in table 1 existing in table 2 and all results of table 2 who do not exist in table1 where Status='Active'
    You may want to use conditions:
    Code:
    ...
     WHERE t1.id IS NOT NULL -- for "all results in table 1 existing in table 2"
       OR  t1.id IS NULL
       AND t2.status = 'Active' -- for "all results of table 2 who do not exist in table1 where Status='Active'"
    ...
    ...
    By applying simple boolean logic, you will konw "t1.id IS NULL AND" is not necessary.
    P1 OR (NOT P1 AND P2) where P1 = "t1.id IS NOT NULL" and P2 = "t2.status = 'Active'"
    := P1 OR P2
    (Assuming no Unknown predicates are included.)


    2) Can we do it inside ON like
    ' ON t2.id = t1.id
    AND t2.status = 'Active' '
    A2-1) rows with status 'Disabled' would not be joined with rows in table1 even if the rows had identical id.
    Because, ON condition selects matching(or joined) rows in inner table(table1), even if the condition is for outer table(table2).

    This may not be a problem, if you don't mind.


    A2-2) Rows in table2 with status 'Disabled' and was not paired with rows in table1 would be included in the result.
    Because, ON condition doesn't affect selection of rows in outer table(table2).
    All rows in outer table would be selected, if no WHERE condition was specified for outer table.

    For example:
    (Add a row (9 , 'Research' , 11 , 'Disabled') to table2)
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH Table1(ID , Name) AS (
    VALUES
      (1 , 'Peter')
    , (2 , 'James')
    , (3 , 'Mark' )
    , (4 , 'Paul' )
    )
    , Table2(DEPTCode , DEPTName , ID , Status) AS (
    VALUES
      (4 , 'Sales'     , 10 , 'Active'  )
    , (5 , 'HR'        ,  1 , 'Active'  )
    , (6 , 'Logistics' ,  2 , 'Active'  )
    , (7 , 'Marketing' ,  3 , 'Disabled')
    , (8 , 'IS'        ,  4 , 'Active'  )
    , (9 , 'Research'  , 11 , 'Disabled')
    )
    SELECT t1.* , t2.*
     FROM  table1 t1
     RIGHT OUTER JOIN
           table2 t2
       ON  t2.id = t1.id
       AND t2.status = 'Active'
    -- WHERE t1.id IS NOT NULL
    --   OR  t2.status = 'Active'
     ORDER BY
           t2.id
    ;
    ------------------------------------------------------------------------------
    
    ID          NAME  DEPTCODE    DEPTNAME  ID          STATUS  
    ----------- ----- ----------- --------- ----------- --------
              1 Peter           5 HR                  1 Active  
              2 James           6 Logistics           2 Active  
              - -               7 Marketing           3 Disabled /* A2-1) */
              4 Paul            8 IS                  4 Active  
              - -               4 Sales              10 Active  
              - -               9 Research           11 Disabled /* A2-2) */
    
      6 record(s) selected.
    Last edited by tonkuma; 07-19-10 at 11:35. Reason: Add more explanations to A1). Add more to A2-1). Add more to A2-2).

  7. #7
    Join Date
    Mar 2009
    Posts
    73

    Smile Thanks so much.

    Thanks so much ! It was of great help and cleared all my confusions. You are always so helpful tonkuma and never angry on basic questions.
    Best wishes.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    stop turning outer join to an inner join

    select a.*,b.* from table1 a RIGHT OUTER JOIN table2 b on a.id=b.id WHERE b.STATUS =1 ORDER BY a.ID DESC;

    The problem is you turned an outer join into an inner join. So, even though you got around this by using an AND/OR, your server is doing a lot more work than required. The proper way to write the condition would be in the ON clause, not the WHERE clause. Such as:

    Code:
    select a.*,b.* 
        from table1 a 
    RIGHT OUTER JOIN table2 b 
         on a.id=b.id 
         and b.STATUS =1
    ORDER BY a.ID DESC
    Try googling outer joins and look for a blog post by Robert Catterall from a few years back, I have posted the link on this forum in the past. Also, look for 2 part white paper written some years back by Terry Purcell, probably one of the best explanations of outer joins out there and referenced quite frequently.
    Dave Nance

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Dave Nance wrote:

    .....
    ..... The proper way to write the condition would be in the ON clause, not the WHERE clause. Such as:

    Code:
    select a.*,b.* 
        from table1 a 
    RIGHT OUTER JOIN table2 b 
         on a.id=b.id 
         and b.STATUS =1
    ORDER BY a.ID DESC
    Dave,
    I think that you forgot what I pointed out in A2-2) of my previous post.

    Here is an example modified a little from my example:
    (It produced an unwanted row with status=9 and no matching row in table1.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH Table1(ID , Name) AS (
    VALUES
      (1 , 'Peter')
    , (2 , 'James')
    , (3 , 'Mark' )
    , (4 , 'Paul' )
    )
    , Table2(DEPTCode , DEPTName , ID , Status) AS (
    VALUES
      (4 , 'Sales'     , 10 , 1)
    , (5 , 'HR'        ,  1 , 1)
    , (6 , 'Logistics' ,  2 , 1)
    , (7 , 'Marketing' ,  3 , 9)
    , (8 , 'IS'        ,  4 , 1)
    , (9 , 'Research'  , 11 , 9)
    )
    select a.*,b.* 
        from table1 a 
    RIGHT OUTER JOIN table2 b 
         on a.id=b.id 
         and b.STATUS =1
    ORDER BY a.ID DESC;
    ------------------------------------------------------------------------------
    
    ID          NAME  DEPTCODE    DEPTNAME  ID          STATUS     
    ----------- ----- ----------- --------- ----------- -----------
              - -               4 Sales              10           1
              - -               7 Marketing           3           9
              - -               9 Research           11           9 /* unwanted row */
              4 Paul            8 IS                  4           1
              2 James           6 Logistics           2           1
              1 Peter           5 HR                  1           1
    
      6 record(s) selected.

  10. #10
    Join Date
    Mar 2009
    Posts
    73
    Thanks Tonkuma, you are absolutely right about that point. A little question, how do you get this syntax to work on ISeries ?
    WITH Table1(ID , Name) AS (
    VALUES
    (1 , 'Peter')
    , (2 , 'James')
    , (3 , 'Mark' )
    , (4 , 'Paul' )
    ) select * from table1;

    It throws error on with clause .

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Not tested.

    Code:
    WITH Table1(ID , Name) AS (
    SELECT 1 , 'Peter' FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 , 'James' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3 , 'Mark'  FROM sysibm.sysdummy1 UNION ALL
    SELECT 4 , 'Paul'  FROM sysibm.sysdummy1
    )
    SELECT * FROM table1
    ;

Posting Permissions

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