Right join with where clause returns wrong results

asp_crazy_guy · #1 (**permalink**) 07-16-10, 02:13

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 ?

tonkuma · #2 (**permalink**) 07-16-10, 04:20

I couldn't understand your requirements.

Is it rows that you wrote columns?

Quote:

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.

asp_crazy_guy · #3 (**permalink**) 07-16-10, 05:01

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

tonkuma · #4 (**permalink**) 07-16-10, 05:32

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.

asp_crazy_guy · #5 (**permalink**) 07-16-10, 05:50

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 =)

tonkuma · #6 (**permalink**) 07-16-10, 08:44

Quote:

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:

Quote:

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.)

Quote:

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.

asp_crazy_guy · #7 (**permalink**) 07-16-10, 10:09

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.

dav1mo · #8 (**permalink**) 07-19-10, 08:11

Quote:

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

tonkuma · #9 (**permalink**) 07-19-10, 10:32

Dave Nance wrote:

Quote:

.....
..... 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.

asp_crazy_guy · #10 (**permalink**) 07-23-10, 02:40

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 .

tonkuma · #11 (**permalink**) 07-23-10, 08:58

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech