Left Outer Join Returning Unqualified Rows



IBM's flagship relational database management system

Left Outer Join Returning Unqualified Rows

Postby Nik22Dec » Wed Dec 28, 2011 5:36 pm

Hi All,

Season's Greetings!!

I am executing a query which Left Outer Joins Table A with Table B. The Query looks like -

Select *
FROM Table A a1
LEFT JOIN Table B b1
ON a1.col1 = b1.col1
AND a1.DT BETWEEN '03/26/2011' AND '03/26/2999'
AND a1.col1='123456'.

The problem which I am facing is that the query is returning rows which do not satisfy the criteria a1.col1='123456' & hence, the unqualified rows, as per my understanding. Can somebody please help me in understanding the rationale behind it. I have already tried putting this conditon a1.col1='123456' with the where clause & then the query works as expected.

Many Thanks in Advance.

Thanks & Regards,
Nik
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Left Outer Join Returning Unqualified Rows

 

Re: Left Outer Join Returning Unqualified Rows

Postby GuyC » Wed Dec 28, 2011 8:00 pm

You already established that following code return the expected (by you) result
Select *
FROM Table A a1
LEFT JOIN Table B b1
ON a1.col1 = b1.col1
where a1.DT BETWEEN '03/26/2011' AND '03/26/2999'
AND a1.col1='123456'.


Now to explain why the first select returns all row of A:
That's what a left join does : it returns all rows on A and then searches for a row in B with "a1.col1 = b1.col1 and a1.DT BETWEEN '03/26/2011' AND '03/26/2999' AND a1.col1='123456'."
If no such row is found in B, it will still return the A-row with empty (null) fields for B-columns

This technique is even sometimes used as a performance trick.
For example if you know a customer of type<>"T" does not have any insurances you can code
select * from customer A left join insurance B on A.type = 'T' and a.icust = b.icust
which should avoid a search on insurance when a.type = 'T'
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Left Outer Join Returning Unqualified Rows

Postby Nik22Dec » Thu Dec 29, 2011 11:19 am

Thanks so much GuyC for your reply. I am so sorry but, I am still not able to understand the logic behind this. I understand that in case of a left join, all the rows of the Left table i.e. Table A in our case will be returned even if there is no corresponding row in Table B but, here, we have put the condition on Table A only.

As per your explanation, a1.col1='123456' condition is not at all honored in case of a Left Join. In that case, what is that we are achieving by using this condition. To summarise, when you say, all rows from A, then, why does the query returns rows having col1 value other than 123456.

Thanks & Regards,
Nik
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Left Outer Join Returning Unqualified Rows

Postby dick scherrer » Thu Dec 29, 2011 12:07 pm

Hello,

If no such row is found in B, it will still return the A-row with empty (null) fields for B-columns

The query is performing as IBM intended. . . Unfortunately, this is not always what we want :)

If some other results are needed and you post an explanation with a bit of data samples, someone may have a suggestion.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Left Outer Join Returning Unqualified Rows

Postby Nik22Dec » Thu Dec 29, 2011 12:27 pm

Hi Dick,

Thanks so much for your reply. Please find below the sample Table data.
Table 1 -
Col1           DT
100           03/26/2012
200           03/26/2012
123456           03/26/2012
500           03/26/2012


Table 2 -
Col1            Col2
100            A
200            B
123456           C
500           D

As per my understanding the query -
Select *
FROM Table A a1
LEFT JOIN Table B b1
ON a1.col1 = b1.col1
AND a1.DT BETWEEN '03/26/2011' AND '03/26/2999'
AND a1.col1='123456'.
should return only the row -

123456 03/26/2012 C

but, I can see 100 & 200 also in the output. That is what has confused me.Please let me know your opinion on the same. I can not modify the query since, it is already there in the production but, need to develop an understanding to predict the result.

Thanks & Regards,
Nik
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Left Outer Join Returning Unqualified Rows

Postby dick scherrer » Thu Dec 29, 2011 12:38 pm

Hello,

I can not modify the query since, it is already there in the production
If the query is wrong - it will need to be changed. . . Giving the business users incorrect data just because some code is already in production is not acceptable anywhere i've ever supported.

Why was the LEFT JOIN used? Suggest you find the business specifications for the process and determine if the code is incorrect or if the returned data is incorrect (or that there is really no problem).

When posting code, jcl, data, etc, you need to use the Code tag. I tried to Code your post, but you had manually "spaced" things which removed the alignment. There is a Preview function that will let you see your post as it will appear to the forum. When the post appears as you want, Submit. A little proctice and becomes automatic.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Left Outer Join Returning Unqualified Rows

Postby GuyC » Thu Dec 29, 2011 1:46 pm

Let me try to explain it this way :
WHERE ... is a SEARCH-condition. If this evaluates to false then the row does not qualify.
ON ... is a JOIN-condition . If this evaluates to false the join fails. In case of a left join : the left row is still returned, the right one isn't. It doesn't matter if in this criteria are columns of table A, B or even from a table that has nothing to do with the join.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Left Outer Join Returning Unqualified Rows

Postby Nik22Dec » Thu Dec 29, 2011 1:59 pm

Hi GuyC,

Thanks for your reply. Now, I understand!! :D :D

Thanks & Regards,
Nik
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post